查询7日内的展示数据

1、需求

2、service层

 /**
     * 趋势图近七天数据获取
     * 1.数据校验 判断仓库是否有效
     * 2.自redis取数，取到数据直接返回
     * 3.redis无数据，去数据库读，返回并根据key存redis
     * @param depotCode
     * @return
     */
    public OverviewAResp overview(String depotCode) {
        //1
        if (ObjectUtils.isEmpty(depotInfoService.getDepotInfoByCode(depotCode.trim()))){
            throw new BizException(BizErrorCode.NO_DATA,"仓库编码不存在");
        }
        //2
        OverviewAResp redisData = (OverviewAResp) redisService.get(REDIS_KEY_FOR + depotCode.trim());
        if (ObjectUtils.isNotEmpty(redisData)) {
            return redisData;
        }
        //3
        OverviewAResp data = overviewDataDeal(depotCode);
        redisService.add(REDIS_KEY_FOR + depotCode.trim(),data, null);
        return overviewDataDeal(depotCode);
    }

    /**
     * 这是一个专门负责取数，服务于楼上和定时任务的方法的方法，没有进行数据校验
     * @param depotCode
     * @return
     */
    public OverviewAResp overviewDataDeal(String depotCode) {
        OverviewAResp overviewAResp = new OverviewAResp();
        overviewAResp.setReceiptInfoDtoList(orderReceiptService.getReceiptInfoDto(depotCode));
        overviewAResp.setOutboundInfoDtoList(orderShipmentService.getOutboundInfoDto(depotCode));
        return overviewAResp;
    }



    /**
     * 深夜计划 每天的凌晨 0：01 刷新各仓库七天的数据存入Redis，
     * 以备前端接口获取数据，不会因数据量而照成响应时间长的情况
     */
    public void refreshOverviewPageDataAllDepotInfo() {
        //获取所有有效仓库信息
        List<DepotInfo> depotInfoList = depotInfoService.getAllDepotInfo();
        SimpleDateFormat df = new SimpleDateFormat(DateUtils.DATE_TIME_PATTERN);
        if (depotInfoList != null && depotInfoList.size() > 0) {
            //根据KEY 覆盖式更新，redis不设置失效时间
            for (DepotInfo depotInfo : depotInfoList) {
                OverviewAResp data = overviewDataDeal(depotInfo.getDepotCode());
                redisService.add(REDIS_KEY_FOR + depotInfo.getDepotCode(),data, null);
            }
            log.info("执行时间：" + df.format(new Date()) + ",此次更新数据的仓库有：" + depotInfoList.toString());
        } else {
            log.error("执行时间：" + df.format(new Date()) + "未获取到仓库信息");
        }

    }

3、sql

SELECT
	a.dt,
	b.addNum,
	c.cancelNUm 
FROM
	( SELECT DATE_SUB( CURRENT_DATE, INTERVAL help_topic_id DAY ) dt FROM mysql.help_topic WHERE help_topic_id BETWEEN 0 AND 6 ) a
	LEFT JOIN (
SELECT
	DATE_FORMAT(create_time,'%Y-%m-%d') AS dt,
	count( * ) AS addNum 
FROM
	order_receipt 
WHERE
	create_time > DATE_SUB( curdate( ), INTERVAL 8 DAY ) 
	AND mark > 0 
	AND order_state NOT IN ( "11" ) 
	AND depot_code = "HMD-051-00" 
GROUP BY
	TO_DAYS( create_time ) 
	) b ON a.dt = b.dt
	LEFT JOIN (
SELECT
	DATE_FORMAT(create_time,'%Y-%m-%d') AS dt,
	count( * ) AS cancelNUm 
FROM
	order_receipt 
WHERE
	create_time > DATE_SUB( curdate( ), INTERVAL 8 DAY ) 
	AND mark > 0 
	AND order_state = "11" 
	AND depot_code = "HMD-051-00" 
GROUP BY
	TO_DAYS( create_time ) 
	) c ON c.dt = a.dt
 ORDER BY a.dt