SpringBoot项目使用EntityManager进行报表统计
当前实现缺陷，如果待统计的数据过多，可能会OOM，分页应该直接在构造的SQL语句中体现，而不是拿回所有数据再分页
如果您统计的数量较少，可以稍加调整进行数据统计，如果数据量较大，分页查询！构造分页SQL！！！
/**
* @Author： Be_insghted
* Description：
* @date Create on 2020/6/15 15:45
**/
@Service
@Slf4j
public class StaticRecordService {
@Autowired
private RecipeCookTaskDao cookTaskDao;
@Autowired
private EntityManager entityManager;
@Autowired
private RecipeService recipeService;
@Autowired
private BusinessCloudConfig businessCloudConfig;
@Autowired
private ShopServer shopServer;
@Autowired
private AdminTokenService tokenService;
@Autowired
private IotProductKey iotProductKey;
@Autowired
private RecipeServer recipeServer;
@Autowired
private RecipeCookTaskEntityMapper recipeCookTaskEntityMapper;
private static final String TASK_ADD_TIME_DEVICE = "addTime";
/**
* All storeNames are for candidate collection
*/
private List<String> storeNames = new CopyOnWriteArrayList<>();
/**
* All storeId matched storeName、storeId、member_id、member_name collection
*/
private Map<String, MerchantInfo2StoreInfoDto> storeId2StoreMainInfoMap = new ConcurrentHashMap<>();
public void setStoreId2StoreMainInfoMap(Map<String, MerchantInfo2StoreInfoDto> storeId2StoreMainInfoMap) {
this.storeId2StoreMainInfoMap = storeId2StoreMainInfoMap;
}
public void setStoreNames(List<String> storeNames) {
this.storeNames = storeNames;
}
public Page<RecordMerchantVO> recipeCookTaskEntityMapper(QueryRecordReq pageInfo) {
PageHelper.startPage(pageInfo.getPage(), pageInfo.getSize());
List<RecordMerchantVO> list = recipeCookTaskEntityMapper.selectMerchantCookTaskTotal(pageInfo);
return new PageImpl<>(list, new PageRequest(pageInfo.getPage() - 1, pageInfo.getSize()), ((com.github.pagehelper.Page) list).getTotal());
}
/**
* 烹饪记录： 根据条件查询商家维度
*
* @param req
*/
public Page<RecordMerchantVO> queryMerchantRecord(QueryRecordReq req) {
String sortWord = req.getSortWord();
String direction = req.getDirection();
Sort sort = new Sort(Sort.Direction.DESC, TASK_ADD_TIME_DEVICE);
Pageable pageable = new PageRequest(req.getPage() - 1, req.getSize(), sort);
if (StringUtils.isNotBlank(req.getStoreName()) || StringUtils.isNotBlank(req.getMerchantId()) || StringUtils.isNotBlank(req.getStoreId())) {
String storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
if (StringUtils.isEmpty(storeId)) {
return new PageImpl<>(new ArrayList<>(), pageable, 0);
}
req.setStoreId(storeId);
}
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
//查询店铺名称、账户，统计店铺总烹饪的次数、 失败（取消+失败）菜谱数、菜譜烹飪成功次数、商家使用拥有菜谱数、设备使用天数
CriteriaQuery<RecordMerchantResult> criteriaQuery = cb.createQuery(RecordMerchantResult.class);
Root<RecipeCookTask> root = criteriaQuery.from(RecipeCookTask.class);
criteriaQuery.multiselect(root.get("storeId"),
cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FAILURE, RecipeCookTask.STATUS.CANCEL)), 1).otherwise(cb.nullLiteral(Number.class))),
cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FINISHED)), 1).otherwise(cb.nullLiteral(Number.class))),
cb.count(root.get("storeId")),
cb.countDistinct((root.get("taskDate"))),
cb.countDistinct(root.get("recipeId"))
);
List<Predicate> predicateList = new ArrayList<>();
if (StringUtils.isNotBlank(req.getMerchantId())) {
// TODO 获取商家的店铺列表 -- storeIds 模拟
List<String> storeIds = Arrays.asList("304108006227476480", "283491778840092672", "306209606672760832", "392849008856612864", "393998821044756480");
if (null != storeIds) {
predicateList.add(root.get("storeId").in(storeIds));
}
}
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getEndTime()));
}
Predicate[] p = new Predicate[predicateList.size()];
criteriaQuery.where(cb.and(predicateList.toArray(p)));
criteriaQuery.groupBy(root.get("storeId"));
List<RecordMerchantResult> counts = entityManager.createQuery(criteriaQuery).getResultList();
counts = sortByProperty(counts, direction, sortWord);
final List<RecordMerchantResult> list = counts;
final List<List<RecordMerchantResult>> partition = Lists.partition(list, pageable.getPageSize());
List<RecordMerchantResult> pageContent;
if (CollectionUtils.isEmpty(list)) {
return new PageImpl<>(new ArrayList<>(0), pageable, 0);
} else {
pageContent = partition.get(pageable.getPageNumber());
}
List<RecordMerchantVO> vos = convertResult2VO(pageContent);
PageImpl<RecordMerchantVO> pageData = new PageImpl<>(vos, pageable, counts.size());
return pageData;
}
public long countMerchantRecords(QueryRecordReq req) {
if (StringUtils.isNotBlank(req.getStoreName()) || StringUtils.isNotBlank(req.getMerchantId()) || StringUtils.isNotBlank(req.getStoreId())) {
String storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
if (StringUtils.isEmpty(storeId)) {
return 0L;
}
req.setStoreId(storeId);
}
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
//查询店铺名称、账户，统计店铺总烹饪的次数、 失败（取消+失败）菜谱数、菜譜烹飪成功次数、商家使用拥有菜谱数、设备使用天数
CriteriaQuery<RecordMerchantResult> criteriaQuery = cb.createQuery(RecordMerchantResult.class);
Root<RecipeCookTask> root = criteriaQuery.from(RecipeCookTask.class);
criteriaQuery.multiselect(root.get("storeId"),
cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FAILURE, RecipeCookTask.STATUS.CANCEL)), 1).otherwise(cb.nullLiteral(Number.class))),
cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FINISHED)), 1).otherwise(cb.nullLiteral(Number.class))),
cb.count(root.get("storeId")),
cb.countDistinct((root.get("taskDate"))),
cb.countDistinct(root.get("recipeId"))
);
List<Predicate> predicateList = new ArrayList<>();
if (!StringUtils.isEmpty(req.getStoreId())) {
predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId()));
}
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get("addTime").as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get("addTime").as(Long.class), req.getEndTime()));
}
Predicate[] p = new Predicate[predicateList.size()];
criteriaQuery.where(cb.and(predicateList.toArray(p)));
criteriaQuery.groupBy(root.get("storeId"));
List<RecordMerchantResult> counts = entityManager.createQuery(criteriaQuery).getResultList();
final List<RecordMerchantResult> list = counts;
if (CollectionUtils.isEmpty(list)) {
return 0L;
}
return counts.size();
}
/**
* 菜谱维度
* @param pageInfo
* @return
*/
public Page<RecordRecipeVO> recipeCookTaskMerchantList(QueryRecipeReq pageInfo) {
PageHelper.startPage(pageInfo.getPage(), pageInfo.getSize());
List<RecordRecipeVO> list = new ArrayList<>();
if (StringUtils.isBlank(pageInfo.getStoreId())
&& StringUtils.isBlank(pageInfo.getStoreName())
&& StringUtils.isBlank(pageInfo.getBindMerchantId())
&& StringUtils.isBlank(pageInfo.getBindMerchantName())) {
list = recipeCookTaskEntityMapper.selectRecipeCookTaskTotal2(pageInfo);
} else {
list = recipeCookTaskEntityMapper.selectRecipeCookTaskTotal(pageInfo);
}
if(CollectionUtils.isNotEmpty(list)){
// 获取当前页数据的创作方、当前同步请求
List<String> recipeIds = list.stream().distinct().map(RecordRecipeVO::getRecipeId).collect(Collectors.toList());
Map<String, String> recipeId2RecipeCreator = recipeServer.listByRecipeIds(recipeIds).getData().stream().collect(Collectors.toMap(StoreRecipeSimpleVo::getRecipeId, StoreRecipeSimpleVo::getPublishStoreName));
for(RecordRecipeVO vo : list){
if (recipeId2RecipeCreator.keySet().contains(vo.getRecipeId())) {
vo.setRecipeCreator(recipeId2RecipeCreator.get(vo.getRecipeId()));
}
}
}
return new PageImpl<>(list, new PageRequest(pageInfo.getPage() - 1, pageInfo.getSize()), ((com.github.pagehelper.Page) list).getTotal());
}
/**
* 菜谱维度明细
* @param pageInfo
* @return
*/
public Page<RecordRecipeDetailVO> selectRecipeCookTaskDetail(QueryRecipeReq pageInfo) {
PageHelper.startPage(pageInfo.getPage(), pageInfo.getSize());
List<RecordRecipeDetailVO> list = recipeCookTaskEntityMapper.selectRecipeCookTaskDetail(pageInfo);
if (CollectionUtils.isNotEmpty(list)) {
for (RecordRecipeDetailVO vo : list) {
if (vo.getTimeConsumed() < 0) {
vo.setTimeConsumed(0);
}
}
}
return new PageImpl<>(list, new PageRequest(pageInfo.getPage() - 1, pageInfo.getSize()), ((com.github.pagehelper.Page) list).getTotal());
}
/**
*
* @param pageInfo
* @return
*/
public Page<RecordDeviceVO> selectRecipeDevicesTaskDetail(QueryDeviceReq pageInfo) {
PageHelper.startPage(pageInfo.getPage(), pageInfo.getSize());
List<RecordDeviceVO> list = recipeCookTaskEntityMapper.selectRecipeDevicesTaskDetail(pageInfo);
return new PageImpl<>(list, new PageRequest(pageInfo.getPage() - 1, pageInfo.getSize()), ((com.github.pagehelper.Page) list).getTotal());
}
/**
* 转换sql查询结果成VO
*
* @param pageContent
* @return
*/
public List<RecordMerchantVO> convertResult2VO(List<RecordMerchantResult> pageContent) {
List<RecordMerchantVO> vos = new ArrayList<>(pageContent.size());
RecordMerchantResult tmp;
for (int i = 0; i < pageContent.size(); i++) {
RecordMerchantVO recordMerchantVO = new RecordMerchantVO();
tmp = pageContent.get(i);
recordMerchantVO.setCuisineCount(tmp.getCuisineCount())
.setErrorCuisineCount(tmp.getErrorCuisineCount())
.setStoreId(tmp.getStoreId())
.setSuccessCuisineCount(tmp.getSuccessCuisineCount())
.setDeviceUsageDays(tmp.getDeviceUsageDays())
.setRecipeUsedCount(tmp.getRecipeUsedCount());
if (storeId2StoreMainInfoMap.keySet().contains(tmp.getStoreId())) {
recordMerchantVO.setStoreName(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getStoreName())
.setMerchantId(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getMerchantId())
.setMerchantName(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getMerchantName());
}
vos.add(recordMerchantVO);
}
return vos;
}
/**
* 排序处理
*
* @param counts
* @param property
* @param sortWord
* @return
*/
public List<RecordMerchantResult> sortByProperty(List<RecordMerchantResult> counts, String property, String sortWord) {
// 降序字段
if (StringUtils.equals(SortDirectionConst.DESC, property)) {
if (StringUtils.equals(sortWord, SortWord.CUISINE_COUNT)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getCuisineCount).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.CUISINE_ERROR)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getErrorCuisineCount).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.CUISINE_SUCCESS)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getSuccessCuisineCount).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.DEVICE_USAGE_DAYS)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getDeviceUsageDays).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.CUISINE_RECIPES)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getRecipeUsedCount).reversed()).collect(Collectors.toList());
}
}
// 升序字段
if (StringUtils.equals(SortDirectionConst.ASC, property)) {
if (StringUtils.equals(sortWord, SortWord.CUISINE_COUNT)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getCuisineCount)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.CUISINE_ERROR)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getErrorCuisineCount)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.CUISINE_SUCCESS)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getSuccessCuisineCount)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.DEVICE_USAGE_DAYS)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getDeviceUsageDays)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.CUISINE_RECIPES)) {
counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getRecipeUsedCount)).collect(Collectors.toList());
}
}
return counts;
}
/**
* use native query
*/
public List<RecordMerchantBackVO> nativeQuery() {
String sql = "select count(1) AS total,\n" +
"sum(case finish_time when 0 then 1 else 0 end) AS success,\n" +
"sum(case finish_time when 0 then 0 else 1 end) AS failure,\n" +
"merchant_id, store_id,\n" +
"COUNT(DISTINCT DATE(task_date)) AS days\n" +
"from recipe_cook_task\n" +
"group by merchant_id, store_id";
Query query = entityManager.createNativeQuery(sql);
//query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(RecordMerchantBackVO.class));
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List list = query.getResultList();
return list;
}
/**
* 烹飪記錄——菜譜維度
*
* @param req edited by Liubo at 20200810
* Description: add column recipe creator for exhibition
* @return
*/
public Page<RecordRecipeVO> queryRecipeRecord(QueryRecipeReq req) {
//新建一个页面，存放页面信息
Sort sort = new Sort(Sort.Direction.DESC, TASK_ADD_TIME_DEVICE);
Pageable page = new PageRequest(req.getPage() - 1, req.getSize(), sort);
if (StringUtils.isNotBlank(req.getStoreName()) || StringUtils.isNotBlank(req.getStoreId()) || StringUtils.isNotBlank(req.getMerchantId())) {
String storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
if (StringUtils.isEmpty(storeId)) {
return new PageImpl<>(new ArrayList<>(), page, 0);
}
req.setStoreId(storeId);
}
String sortWord = req.getSortWord();
String direction = req.getDirection();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<RecordRecipeResult> criteriaQuery = cb.createQuery(RecordRecipeResult.class);
Root<RecipeCookTask> root = criteriaQuery.from(RecipeCookTask.class);
criteriaQuery.multiselect(
root.get("recipeName"),
root.get("recipeId"),
root.get("productKey"),
cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FAILURE, RecipeCookTask.STATUS.CANCEL)), 1).otherwise(cb.nullLiteral(Number.class))),
cb.count(root.get("status")),
cb.countDistinct(root.get("storeId")));
List<Predicate> predicateList = new ArrayList<>();
if (!StringUtils.isEmpty(req.getRecipeId())) {
predicateList.add(cb.equal(root.get("recipeId").as(String.class), req.getRecipeId()));
}
if (!StringUtils.isEmpty(req.getRecipeName())) {
predicateList.add(cb.like(root.get("recipeName").as(String.class), "%" + req.getRecipeName().trim() + "%"));
}
if (!StringUtils.isEmpty(req.getStoreId())) {
predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId()));
}
// if (!StringUtils.isEmpty(req.getMerchantId())) {
// predicateList.add(cb.equal(root.get("merchantId").as(String.class), req.getMerchantId().trim() ));
// }
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get("addTime").as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get("addTime").as(Long.class), req.getEndTime()));
}
Predicate[] p = new Predicate[predicateList.size()];
criteriaQuery.where(cb.and(predicateList.toArray(p)));
// groupBy
criteriaQuery.groupBy(root.get("recipeId"), root.get("recipeName"), root.get("productKey"));
//记录当前sql查询结果总条数
List<RecordRecipeResult> counts = entityManager.createQuery(criteriaQuery).getResultList();
if (CollectionUtils.isEmpty(counts)) {
return new PageImpl<>(new ArrayList<>(0), page, 0);
}
counts = recordRecipeResultsSort(counts, direction, sortWord);
final List<RecordRecipeResult> list = counts;
final List<List<RecordRecipeResult>> partition = Lists.partition(list, page.getPageSize());
List<RecordRecipeResult> pageContent = partition.get(page.getPageNumber());
log.info("CuisineRecordService#queryRecipeRecord 当前页{}<——共{}个菜谱", page, pageContent.size());
// 获取当前页数据的创作方、当前同步请求
List<String> recipeIds = pageContent.stream().distinct().map(RecordRecipeResult::getRecipeId).collect(Collectors.toList());
Map<String, String> recipeId2RecipeCreator = recipeServer.listByRecipeIds(recipeIds).getData().stream().collect(Collectors.toMap(StoreRecipeSimpleVo::getRecipeId, StoreRecipeSimpleVo::getPublishStoreName));
String storeId = req.getStoreId();
String storeName = null;
String merchantId = null;
String merchantName = "";
if (StringUtils.isNotEmpty(storeId) && storeId2StoreMainInfoMap.keySet().contains(storeId)) {
storeName = storeId2StoreMainInfoMap.get(storeId).getStoreName();
merchantId = storeId2StoreMainInfoMap.get(storeId).getMerchantId();
merchantName = storeId2StoreMainInfoMap.get(storeId).getMerchantName();
}
List<RecordRecipeVO> vos = new ArrayList<>();
for (int i = 0; i < pageContent.size(); i++) {
RecordRecipeResult tmp = pageContent.get(i);
RecordRecipeVO vo = new RecordRecipeVO();
vo.setStoreCount(tmp.getStoreCount())
.setRecipeId(tmp.getRecipeId())
.setCuisineCount(tmp.getCuisineCount())
.setProductKey(tmp.getProductKey())
.setErrorCuisineCount(tmp.getErrorCuisineCount())
.setRecipeName(tmp.getRecipeName());
if (StringUtils.isNotEmpty(storeId)) {
vo.setMerchantId(merchantId)
.setStoreId(storeId)
.setStoreName(storeName)
.setMerchantName(merchantName);
}
if (recipeId2RecipeCreator.keySet().contains(tmp.getRecipeId())) {
vo.setRecipeCreator(recipeId2RecipeCreator.get(tmp.getRecipeId()));
}
vos.add(vo);
}
//返回查询的分页结果，createQuery.getResultList()为分页查询的结果对象，counts.size()为设置分页参数之前查询的总数
return new PageImpl<>(vos, page, counts.size());
}
/**
* 菜譜維度 —— 排序
*
* @param counts
* @param direction
* @param sortWord
* @return
*/
List<RecordRecipeResult> recordRecipeResultsSort(List<RecordRecipeResult> counts, String direction, String sortWord) {
if (StringUtils.equals(SortDirectionConst.DESC, direction)) {
if (StringUtils.equals(SortWord.CUISINE_COUNT, sortWord)) {
counts = counts.stream().sorted(Comparator.comparing(RecordRecipeResult::getCuisineCount).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.CUISINE_ERROR)) {
counts = counts.stream().sorted(Comparator.comparing(RecordRecipeResult::getErrorCuisineCount).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.STORE_COUNT)) {
counts = counts.stream().sorted(Comparator.comparing(RecordRecipeResult::getStoreCount).reversed()).collect(Collectors.toList());
}
}
if (StringUtils.equals(SortDirectionConst.ASC, direction)) {
if (StringUtils.equals(SortWord.CUISINE_COUNT, sortWord)) {
counts = counts.stream().sorted(Comparator.comparing(RecordRecipeResult::getCuisineCount)).collect(Collectors.toList());
log.info("排序后ASC{}", counts.stream().map(RecordRecipeResult::getCuisineCount).collect(Collectors.toList()));
}
if (StringUtils.equals(sortWord, SortWord.CUISINE_ERROR)) {
counts = counts.stream().sorted(Comparator.comparing(RecordRecipeResult::getErrorCuisineCount)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.STORE_COUNT)) {
counts = counts.stream().sorted(Comparator.comparing(RecordRecipeResult::getStoreCount)).collect(Collectors.toList());
}
}
return counts;
}
/**
* 烹飪記錄——菜譜明細維度
*
* @param req
* @return
*/
public Page<RecordRecipeDetailVO> queryRecipeDetailRecord(QueryRecipeReq req) {
Sort sort = new Sort(Sort.Direction.DESC, TASK_ADD_TIME_DEVICE);
if (SortDirectionConst.ASC.equals(req.getDirection()) && req.getSortWord().equals(SortWord.COOK_TIME)) {
sort = new Sort(Sort.Direction.ASC, SortWord.COOK_TIME);
}
if (SortDirectionConst.DESC.equals(req.getDirection()) && req.getSortWord().equals(SortWord.COOK_TIME)) {
sort = new Sort(Sort.Direction.DESC, SortWord.COOK_TIME);
}
if (StringUtils.isBlank(req.getDirection()) && StringUtils.isBlank(req.getSortWord())) {
sort = new Sort(Sort.Direction.DESC, TASK_ADD_TIME_DEVICE);
}
Pageable pageable = new PageRequest(req.getPage() - 1, req.getSize(), sort);
if (StringUtils.isNotBlank(req.getStoreId()) || StringUtils.isNotBlank(req.getMerchantId()) || StringUtils.isNotBlank(req.getStoreName())) {
String storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
if (StringUtils.isEmpty(storeId)) {
return new PageImpl<>(new ArrayList<>(), pageable, 0);
}
req.setStoreId(storeId);
}
Page<RecipeCookTask> pageData = cookTaskDao.findAll(getRecipeCookTaskSpecification(req), pageable);
List<RecipeCookTask> list = pageData.getContent();
if (CollectionUtils.isEmpty(list)) {
return new PageImpl<>(new ArrayList<>(0), pageable, 0);
}
List<RecordRecipeDetailVO> vos = convertDBResult2RecipeDetailVO(list);
if (SortDirectionConst.ASC.equals(req.getDirection()) && req.getSortWord().equals(SortWord.TIME_CONSUMED)) {
Integer srcsize = req.getSize();
Integer srcpage = req.getPage();
long total = pageData.getTotalElements();
int size = (int) total;
req.setSize(size);
req.setPage(1);
pageable = new PageRequest(req.getPage() - 1, size);
pageData = cookTaskDao.findAll(getRecipeCookTaskSpecification(req), pageable);
List<RecipeCookTask> content = pageData.getContent();
vos = convertDBResult2RecipeDetailVO(content);
vos = vos.stream().sorted(Comparator.comparingLong(RecordRecipeDetailVO::getTimeConsumed)).collect(Collectors.toList());
log.info("排序后{}", vos.stream().map(RecordRecipeDetailVO::getTimeConsumed).collect(Collectors.toList()));
Page<RecordRecipeDetailVO> rets = ConvertList2PageVOUtils.convertList2PageVO(vos, srcpage, srcsize);
return new PageImpl<>(rets.getContent(), pageable, pageData.getTotalElements());
}
if (SortDirectionConst.DESC.equals(req.getDirection()) && req.getSortWord().equals(SortWord.TIME_CONSUMED)) {
Integer srcsize = req.getSize();
Integer srcpage = req.getPage();
long total = pageData.getTotalElements();
int size = (int) total;
req.setSize(size);
req.setPage(1);
pageable = new PageRequest(req.getPage() - 1, size);
pageData = cookTaskDao.findAll(getRecipeCookTaskSpecification(req), pageable);
List<RecipeCookTask> content = pageData.getContent();
vos = convertDBResult2RecipeDetailVO(content);
vos = vos.stream().sorted(Comparator.comparingLong(RecordRecipeDetailVO::getTimeConsumed).reversed()).collect(Collectors.toList());
log.info("降序排序后{}", vos.stream().map(RecordRecipeDetailVO::getTimeConsumed).collect(Collectors.toList()));
Page<RecordRecipeDetailVO> rets = ConvertList2PageVOUtils.convertList2PageVO(vos, srcpage, srcsize);
return new PageImpl<>(rets.getContent(), pageable, pageData.getTotalElements());
}
return new PageImpl<>(vos, pageable, pageData.getTotalElements());
}
/**
* Dto 装 VO 烹饪明细
*
* @param content
* @return
*/
public List<RecordRecipeDetailVO> convertDBResult2RecipeDetailVO(List<RecipeCookTask> content) {
if (CollectionUtils.isEmpty(content)) {
return new ArrayList<>(0);
}
List<String> recipeIds;
recipeIds = content.stream().distinct().map(RecipeCookTask::getRecipeId).collect(Collectors.toList());
Map<String, String> recipeId2RecipeCreatorMap = recipeServer.listByRecipeIds(recipeIds).getData().stream().distinct().collect(Collectors.toMap(StoreRecipeSimpleVo::getRecipeId, StoreRecipeSimpleVo::getPublishStoreName));
RecipeCookTask task;
List<RecordRecipeDetailVO> vos = new ArrayList<>();
for (int i = 0; i < content.size(); i++) {
task = content.get(i);
RecordRecipeDetailVO detailVO = new RecordRecipeDetailVO();
detailVO.setCookTime(task.getCookTime())
.setDeviceAlias(task.getDeviceAlias())
.setDeviceCgid(task.getDeviceCgid())
.setProductKey(task.getProductKey())
.setRecipeId(task.getRecipeId())
.setRecipeName(task.getRecipeName())
.setStoreId(task.getStoreId())
.setStatus(task.getStatus())
.setId(task.getId());
if (storeId2StoreMainInfoMap.keySet().contains(task.getStoreId())) {
detailVO.setStoreName(storeId2StoreMainInfoMap.get(task.getStoreId()).getStoreName())
.setMerchantId(storeId2StoreMainInfoMap.get(task.getStoreId()).getMerchantId())
.setMerchantName(storeId2StoreMainInfoMap.get(task.getStoreId()).getMerchantName());
}
if (recipeId2RecipeCreatorMap.keySet().contains(task.getRecipeId())) {
detailVO.setRecipeCreator(recipeId2RecipeCreatorMap.get(task.getRecipeId()));
}
if (task.getFinishTime() == 0L && task.getFailTime() == 0L) {
detailVO.setTimeConsumed(0L);
}
if (task.getFailTime() != 0L) {
detailVO.setTimeConsumed(task.getFailTime() - task.getCookTime());
}
if (task.getFinishTime() != 0L) {
detailVO.setTimeConsumed(task.getFinishTime() - task.getCookTime());
}
vos.add(detailVO);
}
return vos;
}
/**
* 设备维度
*
* @param req
* @return
*/
public Page<RecordDeviceVO> queryDeviceRecord(QueryDeviceReq req) {
String sortWord = req.getSortWord();
String direction = req.getDirection();
Sort sort = new Sort(Sort.Direction.DESC, TASK_ADD_TIME_DEVICE);
Pageable page = new PageRequest(req.getPage() - 1, req.getSize(), sort);
if (StringUtils.isNotBlank(req.getMerchantId()) || StringUtils.isNotBlank(req.getStoreId()) || StringUtils.isNotBlank(req.getStoreName())) {
String storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
if (StringUtils.isEmpty(storeId)) {
return new PageImpl<>(new ArrayList<>(), page, 0);
}
req.setStoreId(storeId);
}
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<RecordDeviceResult> criteriaQuery = cb.createQuery(RecordDeviceResult.class);
Root<RecipeCookTask> root = criteriaQuery.from(RecipeCookTask.class);
criteriaQuery.multiselect(
root.get("deviceCgid"),
root.get("deviceAlias"),
root.get("productKey"),
root.get("storeId"),
cb.count(root.get("status"))
);
List<Predicate> predicateList = new ArrayList<>();
if (!StringUtils.isEmpty(req.getStoreId())) {
predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId()));
}
if (!StringUtils.isEmpty(req.getProductKey())) {
predicateList.add(cb.equal(root.get("productKey").as(String.class), req.getProductKey()));
}
if (!StringUtils.isEmpty(req.getDeviceCgid())) {
predicateList.add(cb.equal(root.get("deviceCgid").as(String.class), req.getDeviceCgid()));
}
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getEndTime()));
}
Predicate[] p = new Predicate[predicateList.size()];
criteriaQuery.where(cb.and(predicateList.toArray(p)));
criteriaQuery.groupBy(root.get("deviceCgid"), root.get("deviceAlias"), root.get("productKey"), root.get("storeId"));
List<RecordDeviceResult> counts = entityManager.createQuery(criteriaQuery).getResultList();
counts = recordDeviceResultSort(counts, direction, sortWord);
log.info("设备维度{}", counts.stream().map(RecordDeviceResult::getCuisineCounts).collect(Collectors.toList()));
TypedQuery<RecordDeviceResult> createQuery = entityManager.createQuery(criteriaQuery);
createQuery.setFirstResult((req.getPage() - 1) * req.getSize());
createQuery.setMaxResults(req.getSize());
final List<RecordDeviceResult> list = counts;
final List<List<RecordDeviceResult>> partition = Lists.partition(list, page.getPageSize());
if (CollectionUtils.isEmpty(list)) {
return new PageImpl<>(new ArrayList<>(0), page, 0);
}
List<RecordDeviceResult> pageContent = partition.get(page.getPageNumber());
List<RecordDeviceVO> recordDeviceVOS = new ArrayList<>(pageContent.size());
for (int i = 0; i < pageContent.size(); i++) {
RecordDeviceVO recordDeviceVO = new RecordDeviceVO();
RecordDeviceResult tmp = pageContent.get(i);
recordDeviceVO.setCuisineCounts(tmp.getCuisineCounts())
.setDeviceAlias(tmp.getDeviceAlias())
.setDeviceCgid(tmp.getDeviceCgid())
.setManualCuisine(tmp.getManualCuisine())
.setProductKey(tmp.getProductKey())
.setRecipeCuisineCount(tmp.getRecipeCuisineCount());
if (storeId2StoreMainInfoMap.keySet().contains(tmp.getStoreId())) {
recordDeviceVO.setStoreName(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getStoreName())
.setMerchantId(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getMerchantId())
.setMerchantName(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getMerchantName())
.setStoreId(tmp.getStoreId());
}
recordDeviceVOS.add(recordDeviceVO);
}
log.info("设备维度返回数据：{}", recordDeviceVOS);
return new PageImpl<>(recordDeviceVOS, page, counts.size());
}
/**
* 菜谱维度：按字段全局正序或降序
*
* @param counts
* @param direction
* @param sortWord
* @return
*/
private List<RecordDeviceResult> recordDeviceResultSort(List<RecordDeviceResult> counts, String direction, String sortWord) {
if (StringUtils.equals(direction, SortDirectionConst.DESC)) {
if (StringUtils.equals(sortWord, SortWord.CUISINE_COUNTS)) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getCuisineCounts).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.MANUAL_CUISINE)) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getManualCuisine).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.RECIPE_CUISINE)) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getRecipeCuisineCount).reversed()).collect(Collectors.toList());
}
}
if (StringUtils.equals(direction, SortDirectionConst.ASC)) {
if (StringUtils.equals(sortWord, SortWord.CUISINE_COUNTS)) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getCuisineCounts)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.MANUAL_CUISINE)) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getManualCuisine)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, SortWord.RECIPE_CUISINE)) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getRecipeCuisineCount)).collect(Collectors.toList());
}
}
return counts;
}
public Page<RecordIngredientDto> queryIngredientRecordPage(QueryIngredientReq req) {
PageHelper.startPage(req.getPage(), req.getSize());
List<RecordIngredientDto> list = recipeCookTaskEntityMapper.selectRecipeIngredientTaskDetail(req);
return new PageImpl<>(list, new PageRequest(req.getPage() - 1, req.getSize()), ((com.github.pagehelper.Page) list).getTotal());
}
/**
* 食材调料明细，以recipe_cook_task 为数据源进行统计
*
* @param req
* @return
*/
public Page<RecordIngredientVO> queryIngredientRecord(QueryIngredientReq req) {
Integer page = req.getPage();
Integer size = req.getSize();
if (page < 1 || size < 1) {
return new PageImpl<>(new ArrayList<>(0), new PageRequest(0, 1), 0);
}
Pageable pageable = new PageRequest(page - 1, size);
if (StringUtils.isNotBlank(req.getMerchantId()) || StringUtils.isNotBlank(req.getStoreId()) || StringUtils.isNotBlank(req.getStoreName())) {
if (StringUtils.isEmpty(getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName()))) {
return new PageImpl<>(new ArrayList<>(), pageable, 0);
}
req.setStoreId(getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName()));
}
// 查询所有某店铺或所有店铺菜谱
// 处理菜谱数据、统计食材调料
Map<String, Long> recipeId2Count = statisticRecipeCookTask(req);
if (recipeId2Count == null) {
return new PageImpl<>(new ArrayList<>(), pageable, 0);
}
List<String> recipeIds = new ArrayList<>(recipeId2Count.keySet());
Map<String, List<RecipeIngredientVo>> recipeIngredientsMap = recipeService.getIngredientsMapByRecipeIds(recipeIds);
Map<String, IngredientDatavVo> ingredientDatavVoMap = new HashMap<>();
for (String key : recipeId2Count.keySet()) {
List<RecipeIngredientVo> vos = recipeIngredientsMap.get(key);
if (vos == null) {
continue;
}
for (RecipeIngredientVo vo : vos) {
if (vo.getIngredientQuantity() <= 0.0D) {
continue;
}
convertUnit(vo);
String ingredientUniqueKey = vo.getIngredientName() + "_" + vo.getIngredientUnit() + "_" + vo.getIngredientType();
IngredientDatavVo datavVo = ingredientDatavVoMap.get(ingredientUniqueKey);
if (datavVo == null) {
datavVo = new IngredientDatavVo();
datavVo.setIngredientName(vo.getIngredientName());
datavVo.setIngredientUnit(vo.getIngredientUnit());
datavVo.setIngredientQuantity(vo.getIngredientQuantity() * recipeId2Count.get(key));
datavVo.setIngredientType(vo.getIngredientType());
datavVo.setIngredientId(vo.getIngredientId());
ingredientDatavVoMap.put(ingredientUniqueKey, datavVo);
} else {
datavVo.setIngredientQuantity(datavVo.getIngredientQuantity() + vo.getIngredientQuantity() * recipeId2Count.get(key));
}
}
}
List<IngredientDatavVo> ingredientDatavVos = new ArrayList<>(ingredientDatavVoMap.values());
List<RecordIngredientVO> records = new ArrayList<>();
String storeName = null;
String merchantId = null;
String merchantName = "";
String storeId = req.getStoreId();
if (StringUtils.isNotEmpty(storeId)) {
storeId = req.getStoreId();
if (storeId2StoreMainInfoMap.keySet().contains(storeId)) {
storeName = storeId2StoreMainInfoMap.get(storeId).getStoreName();
merchantId = storeId2StoreMainInfoMap.get(storeId).getMerchantId();
merchantName = storeId2StoreMainInfoMap.get(storeId).getMerchantName();
}
}
for (int i = 0; i < ingredientDatavVos.size(); i++) {
RecordIngredientVO ingredientVO = new RecordIngredientVO();
IngredientDatavVo vo = ingredientDatavVos.get(i);
ingredientVO.setTotal(vo.getIngredientQuantity())
.setUnit(vo.getIngredientUnit())
.setIngredientType(vo.getIngredientType())
.setIngredientName(vo.getIngredientName())
.setIngredientId(vo.getIngredientId());
if (StringUtils.isNotEmpty(req.getStoreId())) {
ingredientVO.setStoreId(storeId)
.setStoreName(storeName)
.setMerchantName(merchantName)
.setMerchantId(merchantId);
}
records.add(ingredientVO);
}
List<RecordIngredientVO> list = records.stream().sorted(Comparator.comparingInt(RecordIngredientVO::getIngredientType)).collect(Collectors.toList());
if (StringUtils.equals(SortDirectionConst.DESC, req.getDirection())) {
list = list.stream().sorted(Comparator.comparingInt(RecordIngredientVO::getIngredientType).thenComparingDouble(RecordIngredientVO::getTotal).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(SortDirectionConst.ASC, req.getDirection())) {
list = list.stream().sorted(Comparator.comparingInt(RecordIngredientVO::getIngredientType).thenComparingDouble(RecordIngredientVO::getTotal)).collect(Collectors.toList());
}
if (StringUtils.isEmpty(req.getIngredientName()) && req.getIngredientId() == null) {
return ConvertList2PageVOUtils.convertList2PageVO(list, pageable);
}
/**
* 根据食材Id、食材名条件筛选
*/
if (req.getIngredientId() != null && StringUtils.isNotBlank(req.getIngredientName())) {
list = list.stream().filter(e -> e.getIngredientName().equals(req.getIngredientName())).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(list)) {
if (list.get(0).getIngredientId().equals(req.getIngredientId())) {
return ConvertList2PageVOUtils.convertList2PageVO(list, pageable);
}
} else {
return ConvertList2PageVOUtils.convertList2PageVO(list, pageable);
}
}
/**
* 筛选对应的食材Id
*/
if (req.getIngredientId() != null) {
list = list.stream().filter(e -> e.getIngredientId().equals(req.getIngredientId())).collect(Collectors.toList());
return ConvertList2PageVOUtils.convertList2PageVO(list, pageable);
}
/**
* 筛选对应的食材名称
*/
if (!StringUtils.isEmpty(req.getIngredientName())) {
list = list.stream().filter(e -> e.getIngredientName().equals(req.getIngredientName())).collect(Collectors.toList());
return ConvertList2PageVOUtils.convertList2PageVO(list, pageable);
}
return ConvertList2PageVOUtils.convertList2PageVO(list, pageable);
}
/**
* 查询商业云店铺信息
*
* @param storeId
* @param merchantId
* @param storeName
* @return
*/
private String getStoreId(String storeId, String merchantId, String storeName) {
// 以商家id、storeName初选数据
if (StringUtils.isNotBlank(storeName) || StringUtils.isNotBlank(merchantId)) {
List<SearchStoresRet> storeRet = searchStoresByParam(storeName, merchantId);
if (storeRet.size() == 1) {// memberId 必定匹配（无需考虑）
if (StringUtils.isNotBlank(storeName)) {//需考虑storeName空否
if (StringUtils.isBlank(storeId)) {
return storeRet.get(0).getStore_name().equals(storeName) ? storeRet.get(0).getStore_id() : null;
}
//需考虑storeId空否,有值需匹配
return storeRet.get(0).getStore_id().equals(storeId) ? storeRet.get(0).getStore_id() : null;
} else {//storeName空,必定是memberId查回来的数据
if (StringUtils.isBlank(storeId)) {//storeId空直接返回
return storeRet.get(0).getStore_id();
}
//storeId非空,匹配数据的storeId
return storeRet.get(0).getStore_id().equals(storeId) ? storeRet.get(0).getStore_id() : null;
}
}
// 以商家id、storeName初选数据为空,没有查到数据直接返回空，无需匹配storeId
if (CollectionUtils.isEmpty(storeRet)) {
return null;
}
// 确定 -> storeName查询，且存在查询字段是别的店铺名的子串现象
if (storeRet.size() > 1) {
// 应用侧是店铺名精确查找、筛选数据
List<SearchStoresRet> dtoList = storeRet.stream().filter(a -> a.getStore_name().equals(storeName)).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(dtoList)) {//精确匹配到数据
if (StringUtils.isBlank(storeId)) {// 需根据storeId匹配数据
return dtoList.get(0).getStore_id();
}
return dtoList.get(0).getStore_id().equals(storeId) ? storeId : null;
} else {
return null;
}
}
}
// memberId、storeName均为空时，可以根据输入的storeId为依据返回，用于应用侧搜索数据
log.info("根据商家账号{}或者店铺名{}或店铺Id{}查询", merchantId, storeName, storeId);
return storeId;
}
@Scheduled(cron = "0 0/30 * * * ? ")
@PostConstruct
public void searchStores() {
String storeName = null;
String memberId = null;
SearchStoresReq searchStoresReqDto = new SearchStoresReq();
searchStoresReqDto.setMember_id(StringUtils.isNotBlank(memberId) ? memberId : "");
searchStoresReqDto.setApp_id(businessCloudConfig.getAppId());
searchStoresReqDto.setStore_name(StringUtils.isNotBlank(storeName) ? storeName : "");
searchStoresReqDto.setOrder("store_addtime");
searchStoresReqDto.setSort(SortDirectionConst.DESC);
searchStoresReqDto.setPageno(1);
searchStoresReqDto.setPagesize(Integer.MAX_VALUE);
List<SearchStoresRet> storesDtos = new ArrayList<>();
try {
BasePageRet<List<SearchStoresRet>> searchStoresRet = shopServer.searchStores(searchStoresReqDto, tokenService.getBizCloudAdminTokenBearer());
if (searchStoresRet != null && searchStoresRet.getCode() == 0 && searchStoresRet.getData() != null) {
storesDtos = searchStoresRet.getData();
}
} catch (Exception e) {
log.error(e.getMessage());
}
Map<String, MerchantInfo2StoreInfoDto> merchant2StoreMap = new ConcurrentHashMap<>();
for (SearchStoresRet s : storesDtos) {
MerchantInfo2StoreInfoDto tmp = new MerchantInfo2StoreInfoDto();
tmp.setMerchantId(s.getMember_id())
.setMerchantName(s.getMember_name())
.setStoreId(s.getStore_id())
.setStoreName(s.getStore_name());
merchant2StoreMap.put(s.getStore_id(), tmp);
}
log.info("Schedule Task done！,查询商业云上店铺数量： {} ", merchant2StoreMap.size());
List<String> stores = storesDtos.stream().map(SearchStoresRet::getStore_name).collect(Collectors.toList());
storeNames.clear();
storeId2StoreMainInfoMap.clear();
setStoreNames(stores);
setStoreId2StoreMainInfoMap(merchant2StoreMap);
}
/**
* 通过店铺名、商家账户搜素店铺
*
* @param storeName
* @param memberId
* @return
*/
public List<SearchStoresRet> searchStoresByParam(String storeName, String memberId) {
SearchStoresReq searchStoresReqDto = new SearchStoresReq();
searchStoresReqDto.setApp_id(businessCloudConfig.getAppId());
searchStoresReqDto.setMember_id(StringUtils.isNotBlank(memberId) ? memberId : "");
searchStoresReqDto.setStore_name(StringUtils.isNotBlank(storeName) ? storeName : "");
searchStoresReqDto.setDistance(null);
searchStoresReqDto.setOrder("store_addtime");
searchStoresReqDto.setSort("DESC");
searchStoresReqDto.setPageno(1);
searchStoresReqDto.setPagesize(Integer.MAX_VALUE);
BasePageRet<List<SearchStoresRet>> searchStoresRet = shopServer.searchStores(searchStoresReqDto, tokenService.getBizCloudAdminTokenBearer());
return searchStoresRet.getData();
}
/**
* 转换食材单位
*
* @param vo
*/
private void convertUnit(RecipeIngredientVo vo) {
String unit = vo.getIngredientUnit();
if (StringUtils.equalsIgnoreCase(unit, UNIT_CONST.GRAM)) {
vo.setIngredientUnit("克");
} else if (StringUtils.equals(unit, UNIT_CONST.KG) || StringUtils.equalsIgnoreCase(unit, UNIT_CONST.KiloGram)) {
vo.setIngredientUnit("克");
vo.setIngredientQuantity(vo.getIngredientQuantity() * 1000);
} else if (StringUtils.equalsIgnoreCase(unit, UNIT_CONST.MILLILITER)) {
vo.setIngredientUnit("毫升");
} else if (StringUtils.equals(unit, UNIT_CONST.LITRE_C) || StringUtils.equalsIgnoreCase(unit, UNIT_CONST.LITRE)) {
vo.setIngredientUnit("毫升");
vo.setIngredientQuantity(vo.getIngredientQuantity() * 1000);
}
}
public interface UNIT_CONST {
String GRAM = "g";
String KiloGram = "kg";
String KG = "千克";
String LITRE_C = "升";
String LITRE = "l";
String MILLILITER = "ml";
}
/**
* 创建EXCEL表格
*
* @return
*/
private HSSFWorkbook createWorkbook(String sheetName, String[] sheetHeaders) {
HSSFWorkbook wb = new HSSFWorkbook();
//创建工作薄
HSSFSheet sheet = wb.createSheet(sheetName);
if (sheetName.contains(ReportDownloadRecordEntity.DIMENSION.MERCHANT.getDimensionName())) {
sheet.setColumnWidth(0, 5000);
sheet.setColumnWidth(1, 6000);
sheet.setColumnWidth(2, 6000);
}
if (sheetName.contains(ReportDownloadRecordEntity.DIMENSION.RECIPE.getDimensionName())) {
sheet.setColumnWidth(0, 8000);
sheet.setColumnWidth(1, 6000);
sheet.setColumnWidth(2, 6000);
sheet.setColumnWidth(3, 6000);
sheet.setColumnWidth(4, 6000);
sheet.setColumnWidth(6, 5000);
sheet.setColumnWidth(7, 5000);
sheet.setColumnWidth(8, 5000);
}
if (sheetName.contains(ReportDownloadRecordEntity.DIMENSION.RECIPE_DETAIL.getDimensionName())) {
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 6000);
sheet.setColumnWidth(2, 4000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 6000);
sheet.setColumnWidth(5, 6000);
sheet.setColumnWidth(8, 6000);
sheet.setColumnWidth(9, 11000);
sheet.setColumnWidth(10, 7000);
}
if (sheetName.contains(ReportDownloadRecordEntity.DIMENSION.INGREDIENT.getDimensionName())) {
sheet.setColumnWidth(2, 5000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 6000);
}
if (sheetName.contains(ReportDownloadRecordEntity.DIMENSION.DEVICE.getDimensionName())) {
sheet.setColumnWidth(0, 11000);
sheet.setColumnWidth(1, 7000);
sheet.setColumnWidth(2, 7000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 6000);
sheet.setColumnWidth(5, 6000);
}
// 表头样式
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setWrapText(true);
//水平居中
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
//垂直居中
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置边框
headerStyle.setBorderTop(CellStyle.BORDER_THIN);
headerStyle.setBorderRight(CellStyle.BORDER_THIN);
headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
//设置颜色
headerStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
//垂直居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置边框
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
HSSFRow row = sheet.createRow(0);
//冻结首行
sheet.createFreezePane(0, 1, 0, 1);
HSSFCell cell;
cellStyle.setFillForegroundColor(HSSFColor.BLACK.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置字体
Font cellFont = wb.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
//自动换行
cellStyle.setWrapText(true);
for (int i = 0; i < sheetHeaders.length; i++) {
cell = row.createCell(i);
cell.setCellValue(sheetHeaders[i]);
row.getCell(i).setCellStyle(headerStyle);
}
return wb;
}
/**
* 将excel对象转换为字节流数组
*
* @param xwb
* @return
* @throws IOException
*/
public byte[] XWorkbook2ByteArray(XSSFWorkbook xwb) throws IOException {
if (null == xwb) {
return null;
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
xwb.write(os);
return os.toByteArray();
}
/**
* 获取String类型时间
*
* @return
*/
public String convertCalender2String() {
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
Calendar calendar = Calendar.getInstance();
return df.format(calendar.getTime());
}
/**
* Excel 表头
*/
public interface SHEET_HEADERS {
String[] MERCHANT = {"商家账号", "商家名称", "商家ID", "店铺名称", "店铺ID", "烹饪菜谱个数", "烹饪次数", "烹饪异常次数", "使用设备的天数"};
String[] RECIPE = {"菜谱名称", "菜谱ID", "创作方", "商家名称", "商家ID", "店铺名称", "店铺ID", "商家数", "烹饪次数", "异常烹饪次数", "设备类型"};
String[] RECIPE_DETAIL = {"菜谱名称", "菜谱ID", "商家账号", "商家名称", "商家ID", "店铺名称", "店铺ID", "烹饪开始时间", "任务耗时", "任务状态", "设备类型", "设备CGID", "设备别名"};
String[] INGREDIENT = {"用料名称", "用料ID", "商家账号", "商家名称", "商家ID", "店铺名称", "店铺ID", "单位", "总量"};
String[] DEVICE = {"设备CGID", "设备别名", "设备类型", "商家账号", "商家名称", "商家ID", "店铺名称", "店铺ID", "总烹饪次数", "菜谱烹饪", "手动烹饪"};
}
/**
* 烹饪记录： 根据条件查询商家维度
*
* @param req
* @return
*/
public List<RecordMerchantVO> listMerchantRecords(QueryRecordReq req) {
if (StringUtils.isNotBlank(req.getMerchantId()) || StringUtils.isNotBlank(req.getStoreName()) || StringUtils.isNotBlank(req.getStoreId())) {
String storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
if (StringUtils.isEmpty(storeId)) {
return new ArrayList<>();
}
req.setStoreId(storeId);
}
String sortWord = req.getSortWord();
String direction = req.getDirection();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<RecordMerchantResult> criteriaQuery = cb.createQuery(RecordMerchantResult.class);
Root<RecipeCookTask> root = criteriaQuery.from(RecipeCookTask.class);
criteriaQuery.multiselect(root.get("storeId"),
cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FAILURE, RecipeCookTask.STATUS.CANCEL)), 1).otherwise(cb.nullLiteral(Number.class))),
cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FINISHED)), 1).otherwise(cb.nullLiteral(Number.class))),
cb.count(root.get("storeId")),
cb.countDistinct((root.get("taskDate"))),
cb.countDistinct(root.get("recipeId"))
);
List<Predicate> predicateList = new ArrayList<>();
if (!StringUtils.isEmpty(req.getStoreId())) {
predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId()));
}
// if (!StringUtils.isEmpty(req.getMerchantId())) {
// predicateList.add(cb.like(root.get("merchantId").as(String.class), "%" + req.getMerchantId().trim() + "%"));
// }
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getEndTime()));
}
Predicate[] p = new Predicate[predicateList.size()];
// 条件筛选
criteriaQuery.where(cb.and(predicateList.toArray(p)));
// 分组
criteriaQuery.groupBy(root.get("storeId"));
TypedQuery<RecordMerchantResult> createQuery = entityManager.createQuery(criteriaQuery);
createQuery.setFirstResult(0);
createQuery.setMaxResults(Integer.MAX_VALUE);
List<RecordMerchantResult> list = entityManager.createQuery(criteriaQuery).getResultList();
log.info("查询商家维度数据大小 {}", list.size());
if (CollectionUtils.isEmpty(list)) {
return new ArrayList<>();
}
list = sortByProperty(list, direction, sortWord);
//返回查询的分页结果，createQuery.getResultList()为分页查询的结果对象，counts.size()为设置分页参数之前查询的总数
List<RecordMerchantVO> vos = convertResult2VO(list);
return vos;
}
/**
* 烹饪记录： 根据条件查询菜谱维度所有数据
*
* @param req
* @return
*/
public List<RecordRecipeVO> listRecipeRecords(QueryRecipeReq req) {
String sortWord = req.getSortWord();
String direction = req.getDirection();
if (StringUtils.isNotBlank(req.getStoreName()) || StringUtils.isNotBlank(req.getStoreId()) || StringUtils.isNotBlank(req.getMerchantId())) {
String storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
if (StringUtils.isEmpty(storeId)) {
return new ArrayList<>();
}
req.setStoreId(storeId);
}
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<RecordRecipeResult> criteriaQuery = cb.createQuery(RecordRecipeResult.class);
Root<RecipeCookTask> root = criteriaQuery.from(RecipeCookTask.class);
criteriaQuery.multiselect(
root.get("recipeName"),
root.get("recipeId"),
root.get("productKey"),
cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FAILURE, RecipeCookTask.STATUS.CANCEL)), 1).otherwise(cb.nullLiteral(Number.class))),
cb.count(root.get("status")),
cb.countDistinct(root.get("storeId")));
List<Predicate> predicateList = new ArrayList<>();
if (!StringUtils.isEmpty(req.getRecipeId())) {
predicateList.add(cb.equal(root.get("recipeId").as(String.class), req.getRecipeId()));
}
if (!StringUtils.isEmpty(req.getRecipeName())) {
predicateList.add(cb.like(root.get("recipeName").as(String.class), "%" + req.getRecipeName().trim() + "%"));
}
if (!StringUtils.isEmpty(req.getStoreId())) {
predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId()));
}
// if (!StringUtils.isEmpty(req.getMerchantId())) {
// predicateList.add(cb.like(root.get("merchantId").as(String.class), "%" + req.getMerchantId().trim() + "%"));
// }
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getEndTime()));
}
Predicate[] p = new Predicate[predicateList.size()];
criteriaQuery.where(cb.and(predicateList.toArray(p)));
criteriaQuery.groupBy(root.get("recipeId"), root.get("recipeName"), root.get("productKey"));
// TODO 查询所有菜谱的创作方
List<RecordRecipeResult> counts = entityManager.createQuery(criteriaQuery).getResultList();
TypedQuery<RecordRecipeResult> createQuery = entityManager.createQuery(criteriaQuery);
createQuery.setFirstResult(0);
createQuery.setMaxResults(Integer.MAX_VALUE);
counts = recordRecipeResultsSort(counts, direction, sortWord);
Map<String, String> recipeId2RecipeCreatorMap = new HashMap<>();
List<String> recipeIds;
recipeIds = counts.stream().distinct().map(RecordRecipeResult::getRecipeId).collect(Collectors.toList());
if (recipeIds.size() <= 10) {
recipeId2RecipeCreatorMap = recipeServer.listByRecipeIds(recipeIds).getData().stream().collect(Collectors.toMap(StoreRecipeSimpleVo::getRecipeId, StoreRecipeSimpleVo::getPublishStoreName));
}
final List<String> list = recipeIds;
final List<List<String>> parts = Lists.partition(list, 10);
for (int i = 0; i < parts.size(); i++) {
// TODO MultiThread
log.info("菜谱维度调试：{}", parts.get(i));
recipeId2RecipeCreatorMap.putAll(recipeServer.listByRecipeIds(parts.get(i)).getData().stream().collect(Collectors.toMap(StoreRecipeSimpleVo::getRecipeId, StoreRecipeSimpleVo::getPublishStoreName)));
}
String storeId = req.getStoreId();
String storeName = null;
String merchantId = null;
String merchantName = "";
if (StringUtils.isNotEmpty(storeId) && storeId2StoreMainInfoMap.keySet().contains(storeId)) {
storeName = storeId2StoreMainInfoMap.get(storeId).getStoreName();
merchantId = storeId2StoreMainInfoMap.get(storeId).getMerchantId();
merchantName = storeId2StoreMainInfoMap.get(storeId).getMerchantName();
}
List<RecordRecipeVO> vos = new ArrayList<>();
for (int i = 0; i < counts.size(); i++) {
RecordRecipeResult tmp = counts.get(i);
RecordRecipeVO vo = new RecordRecipeVO();
if (StringUtils.isNotEmpty(storeId)) {
vo.setMerchantId(merchantId)
.setStoreId(storeId)
.setStoreName(storeName)
.setMerchantName(merchantName);
}
vo.setStoreCount(tmp.getStoreCount())
.setRecipeId(tmp.getRecipeId())
.setCuisineCount(tmp.getCuisineCount())
.setProductKey(tmp.getProductKey())
.setErrorCuisineCount(tmp.getErrorCuisineCount())
.setRecipeName(tmp.getRecipeName());
if (recipeId2RecipeCreatorMap.keySet().contains(tmp.getRecipeId())) {
vo.setRecipeCreator(recipeId2RecipeCreatorMap.get(tmp.getRecipeId()));
}
vos.add(vo);
}
return vos;
}
/**
* 烹饪记录： 根据条件查询菜谱烹饪明细所有数据
*
* @param req
* @return
*/
public List<RecordRecipeDetailVO> listRecipeDetailRecords(QueryRecipeReq req) {
req.setPage(1);
req.setSize(Integer.MAX_VALUE);
log.info("导出明细请求参数：{}", req);
Page<RecordRecipeDetailVO> pageData = queryRecipeDetailRecord(req);
List<RecordRecipeDetailVO> list = pageData.getContent();
return list;
}
/**
* 烹饪记录： 根据条件查询设备维度所有数据
*
* @param req
* @return
*/
public List<RecordDeviceVO> listDeviceVOs(QueryDeviceReq req) {
String sortWord = req.getSortWord();
String direction = req.getDirection();
String storeId;
if (StringUtils.isNotBlank(req.getStoreName()) || StringUtils.isNotBlank(req.getStoreId()) || StringUtils.isNotBlank(req.getMerchantId())) {
storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
if (StringUtils.isEmpty(storeId)) {
return new ArrayList<>();
}
req.setStoreId(storeId);
}
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<RecordDeviceResult> criteriaQuery = cb.createQuery(RecordDeviceResult.class);
Root<RecipeCookTask> root = criteriaQuery.from(RecipeCookTask.class);
criteriaQuery.multiselect(
root.get("deviceCgid"),
root.get("deviceAlias"),
root.get("productKey"),
root.get("storeId"),
cb.count(root.get("status"))
);
List<Predicate> predicateList = new ArrayList<>();
if (!StringUtils.isEmpty(req.getStoreId())) {
predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId()));
}
if (!StringUtils.isEmpty(req.getProductKey())) {
predicateList.add(cb.equal(root.get("productKey").as(String.class), req.getProductKey()));
}
if (!StringUtils.isEmpty(req.getDeviceCgid())) {
predicateList.add(cb.equal(root.get("deviceCgid").as(String.class), req.getDeviceCgid()));
}
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get("addTime").as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get("addTime").as(Long.class), req.getEndTime()));
}
Predicate[] p = new Predicate[predicateList.size()];
criteriaQuery.where(cb.and(predicateList.toArray(p)));
criteriaQuery.groupBy(root.get("deviceCgid"), root.get("deviceAlias"), root.get("productKey"), root.get("storeId"));
List<RecordDeviceResult> counts = entityManager.createQuery(criteriaQuery).getResultList();
if ("DESC".equals(direction)) {
if (StringUtils.equals(sortWord, "recipeCuisineCount")) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getRecipeCuisineCount).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, "cuisineCounts")) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getCuisineCounts).reversed()).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, "manualCuisine")) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getManualCuisine).reversed()).collect(Collectors.toList());
}
}
if ("ASC".equals(direction)) {
if (StringUtils.equals(sortWord, "cuisineCounts")) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getCuisineCounts)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, "recipeCuisineCount")) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getRecipeCuisineCount)).collect(Collectors.toList());
}
if (StringUtils.equals(sortWord, "manualCuisine")) {
counts = counts.stream().sorted(Comparator.comparing(RecordDeviceResult::getManualCuisine)).collect(Collectors.toList());
}
}
List<RecordDeviceVO> recordDeviceVOS = new ArrayList<>(counts.size());
for (int i = 0; i < counts.size(); i++) {
RecordDeviceVO recordDeviceVO = new RecordDeviceVO();
RecordDeviceResult tmp = counts.get(i);
recordDeviceVO.setCuisineCounts(tmp.getCuisineCounts())
.setProductKey(tmp.getProductKey())
.setDeviceAlias(tmp.getDeviceAlias())
.setDeviceCgid(tmp.getDeviceCgid())
.setManualCuisine(tmp.getManualCuisine())
.setRecipeCuisineCount(tmp.getRecipeCuisineCount())
.setStoreId(tmp.getStoreId());
if (storeId2StoreMainInfoMap.keySet().contains(tmp.getStoreId())) {
recordDeviceVO.setStoreName(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getStoreName())
.setMerchantId(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getMerchantId())
.setMerchantName(storeId2StoreMainInfoMap.get(tmp.getStoreId()).getMerchantName());
}
recordDeviceVOS.add(recordDeviceVO);
}
return recordDeviceVOS;
}
/**
* 导出商家维度记录
*
* @param merchantVOS
* @return
*/
public HSSFWorkbook exportMerchantData(List<RecordMerchantVO> merchantVOS) {
String sheetName = ReportDownloadRecordEntity.DIMENSION.MERCHANT.getDimensionName().concat(convertCalender2String());
HSSFWorkbook wb = createWorkbook(sheetName, SHEET_HEADERS.MERCHANT);
if (wb == null) {
throw new BaseRetException(BaseRet.createFailureRet("创建EXCEL失败!"));
}
int rowNum = 1;
HSSFSheet sheet = wb.getSheet(sheetName);
if (sheet == null) {
throw new BaseRetException(BaseRet.createFailureRet("获取EXCEL工作簿失败!"));
}
if (CollectionUtils.isEmpty(merchantVOS)) {
return wb;
}
HSSFCellStyle cellStyle = wb.createCellStyle();
for (RecordMerchantVO vo : merchantVOS) {
if (null == vo) {
continue;
}
Integer cellNum = 0;
HSSFRow row = sheet.createRow(rowNum++);
// "商家账号"
HSSFCell cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getMerchantId());
//"店铺名称"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreName());
// "店铺ID"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreId());
// 菜谱烹饪个数
// 烹饪菜谱个数（统计商家烹饪的菜谱个数，如果商家有**100**个，使用了其中**80**个烹饪总次数2000，展示为**80**）
// 烹饪菜谱次数（统计商家烹饪的菜谱个数，如果商家有**100**个，使用了其中80个烹饪总次数**2000**，展示为**2000**）
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getRecipeUsedCount());
//"烹饪次数"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getCuisineCount());
// "烹饪异常次数"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getErrorCuisineCount());
// 设备使用天数
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getDeviceUsageDays());
}
return wb;
}
/**
* 导出菜谱数据
*
* @param recipeResults
* @return
*/
public HSSFWorkbook exportRecipeData(List<RecordRecipeVO> recipeResults) {
String sheetName = ReportDownloadRecordEntity.DIMENSION.RECIPE.getDimensionName().concat(convertCalender2String());
HSSFWorkbook wb = createWorkbook(sheetName, SHEET_HEADERS.RECIPE);
if (wb == null) {
throw new BaseRetException(BaseRet.createFailureRet("创建EXCEL失败!"));
}
int rowNum = 1;
HSSFSheet sheet = wb.getSheet(sheetName);
if (sheet == null) {
throw new BaseRetException(BaseRet.createFailureRet("获取EXCEL工作簿失败!"));
}
if (CollectionUtils.isEmpty(recipeResults)) {
return wb;
}
HSSFCellStyle cellStyle = wb.createCellStyle();
for (RecordRecipeVO vo : recipeResults) {
if (null == vo) {
continue;
}
Integer cellNum = 0;
HSSFRow row = sheet.createRow(rowNum++);
// "菜谱名称"
HSSFCell cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getRecipeName());
//"菜谱ID"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getRecipeId());
// 店铺名称
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreName());
// 店铺ID
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreId());
// // 商家账号
// cell = row.createCell(cellNum++);
// cell.setCellStyle(cellStyle);
// cell.setCellValue(vo.getMerchantId());
// "创作方"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getRecipeCreator());
// "商家数"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreCount());
//"烹饪次数"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getCuisineCount());
// "烹饪异常次数"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getErrorCuisineCount());
// 设备类型
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(iotProductKey.getProductKey2Name().get(vo.getProductKey()));
}
return wb;
}
/**
* 菜谱明细
*
* @param recipeDetailVOS
* @return
*/
public HSSFWorkbook exportRecipeDetailData(List<RecordRecipeDetailVO> recipeDetailVOS) {
String sheetName = ReportDownloadRecordEntity.DIMENSION.RECIPE_DETAIL.getDimensionName().concat(convertCalender2String());
HSSFWorkbook wb = createWorkbook(sheetName, SHEET_HEADERS.RECIPE_DETAIL);
if (wb == null) {
throw new BaseRetException(BaseRet.createFailureRet("创建EXCEL失败!"));
}
int rowNum = 1;
HSSFSheet sheet = wb.getSheet(sheetName);
if (sheet == null) {
throw new BaseRetException(BaseRet.createFailureRet("获取EXCEL工作簿失败!"));
}
if (CollectionUtils.isEmpty(recipeDetailVOS)) {
return wb;
}
HSSFCellStyle cellStyle = wb.createCellStyle();
SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
for (RecordRecipeDetailVO vo : recipeDetailVOS) {
if (null == vo) {
continue;
}
Integer cellNum = 0;
HSSFRow row = sheet.createRow(rowNum++);
// "菜谱名称"
HSSFCell cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getRecipeName());
//"菜谱ID"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getRecipeId());
// "商家账号"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getMerchantId());
//"店铺名称"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreName());
//"店铺ID"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreId());
// "烹饪开始时间"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
long cookTime = vo.getCookTime();
if (cookTime == 0L) {
cell.setCellValue("--");
} else {
cell.setCellValue(df.format(cookTime));
}
// "烹饪耗时"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
long timeConsumed = (vo.getTimeConsumed());
if (timeConsumed == 0L) {
cell.setCellValue("--");
} else {
cell.setCellValue(TimeUtils.getTimeString(timeConsumed));
}
// 任务状态
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
String status;
switch (vo.getStatus()) {
case 1:
status = "烹饪中";
break;
case 2:
status = "烹饪完成";
break;
case 3:
status = "烹饪失败";
break;
case 4:
status = "取消";
break;
default:
status = "未开始";
}
cell.setCellValue(status);
// 设备类型
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(iotProductKey.getProductKey2Name().get(vo.getProductKey()));
// 设备CGID
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getDeviceCgid());
// 设备别名
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getDeviceAlias());
}
return wb;
}
/**
* 食材调料
*
* @param vos
* @return
*/
public HSSFWorkbook exportIngredientData(List<RecordIngredientVO> vos) {
String sheetName = ReportDownloadRecordEntity.DIMENSION.INGREDIENT.getDimensionName().concat(convertCalender2String());
HSSFWorkbook wb = createWorkbook(sheetName, SHEET_HEADERS.INGREDIENT);
if (wb == null) {
throw new BaseRetException(BaseRet.createFailureRet("创建EXCEL失败!"));
}
int rowNum = 1;
HSSFSheet sheet = wb.getSheet(sheetName);
if (sheet == null) {
throw new BaseRetException(BaseRet.createFailureRet("获取EXCEL工作簿失败!"));
}
if (CollectionUtils.isEmpty(vos)) {
return wb;
}
HSSFCellStyle cellStyle = wb.createCellStyle();
log.info("食材名称{}", vos.stream().map(RecordIngredientVO::getIngredientName).collect(Collectors.toList()));
for (RecordIngredientVO vo : vos) {
// if (null == vo) {
// continue;
// }
log.info("食材名称：{}", vo.getIngredientName());
Integer cellNum = 0;
HSSFRow row = sheet.createRow(rowNum++);
// "食材名称"
HSSFCell cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getIngredientName());
//"食材ID"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getIngredientId());
// "商家账号"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getMerchantId());
//"店铺名称
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreName());
// 店铺ID
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreId());
// 单位
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getUnit());
//"总量"
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getTotal());
}
return wb;
}
/**
* 设备烹饪数据
*
* @param vos
* @return
*/
public HSSFWorkbook exportDeviceData(List<RecordDeviceVO> vos) {
String sheetName = ReportDownloadRecordEntity.DIMENSION.DEVICE.getDimensionName().concat(convertCalender2String());
HSSFWorkbook wb = createWorkbook(sheetName, SHEET_HEADERS.DEVICE);
if (wb == null) {
throw new BaseRetException(BaseRet.createFailureRet("创建EXCEL失败!"));
}
int rowNum = 1;
HSSFSheet sheet = wb.getSheet(sheetName);
if (sheet == null) {
throw new BaseRetException(BaseRet.createFailureRet("获取EXCEL工作簿失败!"));
}
if (CollectionUtils.isEmpty(vos)) {
return wb;
}
HSSFCellStyle cellStyle = wb.createCellStyle();
for (RecordDeviceVO vo : vos) {
if (null == vo) {
continue;
}
Integer cellNum = 0;
HSSFRow row = sheet.createRow(rowNum++);
// 设备CGID
HSSFCell cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getDeviceCgid());
// 设备别名
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getDeviceAlias());
// 设备类型
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(iotProductKey.getProductKey2Name().get(vo.getProductKey()));
// 商家账号
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getMerchantId());
// 店铺名称
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreName());
// 店铺ID
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getStoreId());
// 总烹饪次数
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getCuisineCounts());
// 菜谱烹饪
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getRecipeCuisineCount());
// 手动烹饪
cell = row.createCell(cellNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(vo.getManualCuisine());
}
return wb;
}
/**
* 统计菜谱烹饪数据 recipeId ：SuccessCuisineCounts
*
* @param req
* @return
*/
public Map<String, Long> statisticRecipeCookTask(QueryIngredientReq req) {
Map<String, Long> map;
List<RecipeCookTask> list = cookTaskDao.findAll(getRecipeCookTaskStatisticSpecification(req));
if (StringUtils.isEmpty(req.getStoreId())) {
map = list.stream().collect(Collectors.groupingBy(RecipeCookTask::getRecipeId, Collectors.counting()));
} else {
map = list.stream().filter(r -> req.getStoreId().equals(r.getStoreId())).collect(Collectors.groupingBy(RecipeCookTask::getRecipeId, Collectors.counting()));
}
log.info("成功烹饪的菜谱个数为：{}", map.size());
return map;
}
/**
* 食材搜索
*
* @param req
* @return
*/
public Specification<RecipeCookTask> getRecipeCookTaskStatisticSpecification(QueryIngredientReq req) {
return (root, query, cb) -> {
List<Predicate> predicateList = new ArrayList<>();
// 菜谱名
if (!StringUtils.isEmpty(req.getStoreId())) {
predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId()));
}
// if (!StringUtils.isEmpty(req.getMerchantId())) {
// predicateList.add(cb.like(root.get("merchantId").as(String.class), "%" + req.getMerchantId().trim() + "%"));
// }
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getEndTime()));
}
predicateList.add(cb.equal(root.get("status").as(Integer.class), RecipeCookTask.STATUS.FINISHED));
Predicate[] p = new Predicate[predicateList.size()];
return cb.and(predicateList.toArray(p));
};
}
/**
* 按店铺id、商家Id(精确查找)设备cgid精确、设备别名、设备类型模糊搜索
**/
public Specification<RecipeCookTask> getRecipeCookTaskSpecification(QueryRecipeReq req) {
return (root, query, cb) -> {
List<Predicate> predicateList = new ArrayList<>();
// 菜谱名
if (!StringUtils.isEmpty(req.getRecipeName())) {
predicateList.add(cb.like(root.get("recipeName").as(String.class), "%" + req.getRecipeName().trim() + "%"));
}
// 菜谱Id
if (!StringUtils.isEmpty(req.getRecipeId())) {
predicateList.add(cb.equal(root.get("recipeId").as(String.class), req.getRecipeId().trim()));
}
if (!StringUtils.isEmpty(req.getProductKey())) {
log.info("设备类型：{}", req.getProductKey());
predicateList.add(cb.equal(root.get("productKey").as(String.class), req.getProductKey().trim()));
}
if (!StringUtils.isEmpty(req.getDeviceCgid())) {
predicateList.add(cb.equal(root.get("deviceCgid").as(String.class), req.getDeviceCgid().trim()));
}
if (!StringUtils.isEmpty(req.getStoreId())) {
predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId().trim()));
}
if (req.getStartTime() != null && req.getEndTime() != null) {
predicateList.add(cb.greaterThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getStartTime()));
predicateList.add(cb.lessThanOrEqualTo(root.get(TASK_ADD_TIME_DEVICE).as(Long.class), req.getEndTime()));
}
Predicate[] p = new Predicate[predicateList.size()];
return cb.and(predicateList.toArray(p));
};
}
/**
* 批量处理数据
*
* @param req
*/
public void patch(PatchReq req) {
Pageable pageable = new PageRequest(req.getPage() - 1, req.getSize());
List<RecipeCookTask> tasks = cookTaskDao.findAll(pageable).getContent();
List<RecipeCookTask> tasksDes = new ArrayList<>(tasks.size());
SimpleDateFormat df = new SimpleDateFormat("y-M-d");
for (int i = 0; i < tasks.size(); i++) {
RecipeCookTask t = tasks.get(i);
long time = t.getAddTime();
if (time == 0) {
time = t.getCreateTime().getTime();
}
String dateString = df.format(time);
try {
t.setTaskDate(df.parse(dateString));
} catch (ParseException e) {
e.printStackTrace();
}
tasksDes.add(t);
}
cookTaskDao.save(tasksDes);
}
/**
* 候选匹配度相似最高的数据
* <p>
* TODO： 当店铺数量较多时,需按一定规则匹配数据
*
* @param storeName
* @return
*/
public List<String> getCandidateStores(String storeName) {
if (StringUtils.isBlank(storeName)) {
return new ArrayList<>(0);
}
List<String> allCandidates = storeNames.stream().filter(s -> s.contains(storeName)).collect(Collectors.toList());
if (CollectionUtils.isEmpty(allCandidates)) {
return new ArrayList<>(0);
}
log.info("所有包含关键字的字符串： {}", allCandidates);
List<MatchingDegreeVO> associates = new ArrayList<>();
for (int i = 0; i < allCandidates.size(); i++) {
String associateWord = allCandidates.get(i);
Double matchingDegree = (double) storeName.length() / (double) associateWord.length();
MatchingDegreeVO tmp = new MatchingDegreeVO();
tmp.setAssociateWord(associateWord).setMatchingDegree(matchingDegree);
associates.add(tmp);
}
// 当前店铺数较少、全部显示
List<MatchingDegreeVO> collect = associates.stream().sorted(Comparator.comparingDouble(MatchingDegreeVO::getMatchingDegree).reversed()).collect(Collectors.toList());
log.info("候选对象及匹配度：{}", collect);
return collect.stream().map(MatchingDegreeVO::getAssociateWord).collect(Collectors.toList());
}
}
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
