前言
用于記錄蒼穹外賣Day10、Day11、Day12的學習
Day10 訂單狀態定時處理 來電提醒 客戶催單
訂單狀態定時處理
Spring Task
Spring Task是一個任務調度工具,可以按照約定的時間自動執行某個代碼邏輯(定時自動執行某段Java代碼)
cron表達式:
cron表達式其實就是一個字符串,通過cron表達式可以定義任務觸發的時間。
構成規則:分為6或7個域,用空格隔開,每個域代表一個含義。從左到右依次為秒、分鐘、小時、日、月、周、年(可選)
cron在線生成器:https://cron.qqe2.com
Spring Task使用步驟:
- 導入坐標spring-context
- 啟動類添加注解@EnableScheduling開啟任務調度
- 自定義定時任務類
需求開發
存在的問題:
- 下單后未支付,訂單一直處于”待支付“狀態
- 用戶收貨后管理端未點擊完成按鈕,訂單一直處于”派送中“狀態
只需自定義個任務處理類來定時處理即可:
//定時任務類,定時處理訂單狀態
@Component
@Slf4j
public class OrderTask {@Autowiredprivate OrderMapper orderMapper;//處理下單后未支付超時的情況@Scheduled(cron = "0 * * * * ? *")//每分鐘觸發一次
// @Scheduled(cron="0/5 * * * * ?")public void processTimeOut(){log.info("定時處理下單未支付的訂單");//當前時間減15分鐘LocalDateTime localDateTime = LocalDateTime.now().plusMinutes(-15);List<Orders> list = orderMapper.getByStatusAndOrderTimeLT(Orders.PENDING_PAYMENT, localDateTime);if(list!=null&&list.size()>0){for (Orders orders : list) {orders.setStatus(Orders.CANCELLED);orders.setConsignee("訂單超時,自動取消");orders.setCancelTime(LocalDateTime.now());orderMapper.update(orders);}}}//處理一直處于派送中,沒有完成的訂單@Scheduled(cron = "0 0 1 * * ?")//每天凌晨一點觸發
// @Scheduled(cron="0/10 * * * * ?")public void processDeliveryOrder(){log.info("定時處理一直在派送的訂單");LocalDateTime localDateTime = LocalDateTime.now().plusMinutes(-60);List<Orders> list = orderMapper.getByStatusAndOrderTimeLT(Orders.DELIVERY_IN_PROGRESS, localDateTime);if(list!=null&&list.size()>0){for (Orders orders : list) {orders.setStatus(Orders.COMPLETED);orderMapper.update(orders);}}}
}
來電提醒
WebSocket
WebSocket是基于TCP的一種新的網絡協議。它實現了瀏覽器與服務器全雙工通信–瀏覽器和服務器只需完成一次握手,兩者之間即可建立持久性的連接,并進行雙向數據傳輸。
WebSocket和HTTP對比:
- HTTP是短連接;WebSocket是長連接
- HTTP是單向通信,基于請求響應模型;WebSocket支持雙向通信。
- WebSocket和HTTP都是基于TCP協議
應用場景:
- 視頻彈幕
- 實況更新
- 網頁聊天
需求開發
實現思路:
- 通過WebSocket實現管理端頁面和服務端保持長連接
- 當客戶支付后,調用WebSocket的相關API從服務端向客戶端推送消息
- 客戶端解析服務端發送的消息,判斷是來電提醒還是客戶催單,并進行相應的語音播報
- 約定服務端向客戶端發送的消息的數據格式為JSON,字段包括:type(消息類型,1為來單提醒、2為客戶催單)、orderId、content(消息內容)
這里我們只需要在支付成功后提示管理端即可,在OrderServiceImpl的paySuccess方法中:
//通過WebSocket向客戶端瀏覽器推送數據Map map=new HashMap();map.put("type",1);map.put("orderId",ordersDB.getId());map.put("content","訂單號:"+outTradeNo);String Json= JSON.toJSONString(map);webSocketServer.sendToAllClient(Json);
注意:啟動項目的時候看看你是否連接上WebSocket,如果沒連接上可能是因為自己修改過端口號的問題,將端口號改回80或者改下前端代碼即可。
客戶催單
實現思路和來電提醒差不多。當用戶在客戶端點擊催單按鈕時,發起請求
- OrderController
@GetMapping("/reminder/{id}")@ApiOperation("客戶催單")public Result reminder(@PathVariable Long id){orderService.reminder(id);return Result.success();}
- OrderServiceImpl
public void reminder(Long id) {// 根據id查詢訂單Orders orders1= orderMapper.getById(id);// 校驗訂單是否存在if (orders1 == null) {throw new OrderBusinessException(MessageConstant.ORDER_STATUS_ERROR);}//通過WebSocket向客戶端瀏覽器推送數據Map map=new HashMap();map.put("type",2);map.put("orderId",id);map.put("content","訂單號:"+orders1.getNumber());String Json= JSON.toJSONString(map);webSocketServer.sendToAllClient(Json);}
Day11 數據統計-圖形報表
效果如下所示:
Apache ECharts
Apache ECharts是一款基于JavaScript的數據可視化圖表庫,提供直觀、生動、可交互、可個性化定制的數據可視化圖表。簡單來說,它就是一款數據可視化工具。我們只需大致知道它是干啥的,它是在前端使用的,后端開發中我們使用不到。
營業額統計
業務規則:
- 營業額指訂單狀態為已完成的訂單金額合計
- 基于可視化報表的折線圖展示營業額數據,x軸為日期,y軸為營業額
- 根據時間選擇區間,展示每天的營業額數據
ReportController:注意時間的數據格式
@RestController
@Slf4j
@RequestMapping("/admin/report")
@Api(tags = "數據統計相關接口")
public class ReportController {@Autowiredprivate ReportService reportService;@GetMapping("/turnoverStatistics")@ApiOperation("營業額統計")public Result<TurnoverReportVO> turnoverStatistics(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){log.info("營業額數據統計:{},{}",begin,end);TurnoverReportVO turnoverReportVO=reportService.getTurnoverStatistics(begin,end);return Result.success(turnoverReportVO);}
}
ReportServiceImpl:
這里我的實現方法與課程中略有不同,可以參考一下
public TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end) {TurnoverReportVO turnoverReportVO=new TurnoverReportVO();//1.封裝日期數據//先去得到一個日期集合,包含begin到end中的所有日期List<LocalDate> dateList=new ArrayList<>();dateList.add(begin);while(!begin.equals(end)){begin=begin.plusDays(1);dateList.add(begin);}//將集合轉成字符串的同時在每個元素間加一個逗號String dateList1=StringUtils.join(dateList,",");turnoverReportVO.setDateList(dateList1);//2.封裝營業額數據//查詢對應日期的訂單的總營業額List<Double> moneyList=new ArrayList<>();for (LocalDate localDate : dateList) {//根據日期查詢狀態為已完成的訂單的營業額//00:00:00LocalDateTime beginTime=LocalDateTime.of(localDate, LocalTime.MIN);//23:59:59LocalDateTime endTime=LocalDateTime.of(localDate, LocalTime.MAX);Map map=new HashMap();map.put("begin",beginTime);map.put("end",endTime);map.put("status", Orders.COMPLETED);Double money=orderMapper.getSumByMap(map);if(money==null){money=0.0;}moneyList.add(money);}String moneyList1=StringUtils.join(moneyList,",");turnoverReportVO.setTurnoverList(moneyList1);return turnoverReportVO;}
用戶統計
ReportController:
@GetMapping("/userStatistics")@ApiOperation("用戶統計")public Result<UserReportVO> userStatistics(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin, @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){log.info("用戶統計:{},{}",begin,end);UserReportVO userReportVO=reportService.getUserStatistics(begin,end);return Result.success(userReportVO);}
ReportServiceImpl:
//用戶數據統計public UserReportVO getUserStatistics(LocalDate begin, LocalDate end) {UserReportVO userReportVO=new UserReportVO();//1.封裝日期數據//先去得到一個日期集合,包含begin到end中的所有日期List<LocalDate> dateList=new ArrayList<>();dateList.add(begin);while(!begin.equals(end)){begin=begin.plusDays(1);dateList.add(begin);}//將集合轉成字符串的同時在每個元素間加一個逗號String dateList1=StringUtils.join(dateList,",");userReportVO.setDateList(dateList1);//2.封裝用戶總量數據List<Integer> totalList=new ArrayList<>();//3.封裝新增用戶數量數據List<Integer> newList=new ArrayList<>();for (LocalDate localDate : dateList) {//查詢用戶表createTime這一天的用戶的總量//00:00:00LocalDateTime beginTime=LocalDateTime.of(localDate, LocalTime.MIN);//23:59:59LocalDateTime endTime=LocalDateTime.of(localDate, LocalTime.MAX);Map map=new HashMap();map.put("end",endTime);Integer total=userMapper.countByMap(map);if(total==null){total=0;}totalList.add(total);map.put("begin",beginTime);Integer today= userMapper.countByMap(map);if(today==null){today=0;}newList.add(today);}String userList1=StringUtils.join(totalList,",");userReportVO.setTotalUserList(userList1);String list1=StringUtils.join(newList,",");userReportVO.setNewUserList(list1);return userReportVO;}
訂單統計
業務規則:
- 兩條折線,一條代表總訂單數,另一條代表有效訂單數(狀態為已完成的訂單)
- 展示訂單總數、有效訂單數、訂單完成率數據
ReportController:
@GetMapping("/ordersStatistics")@ApiOperation("訂單統計")public Result<OrderReportVO> orderStatistics(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin, @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){log.info("訂單統計:{},{}",begin,end);OrderReportVO orderReportVO=reportService.getOrderReportStatistics(begin,end);return Result.success(orderReportVO);}
ReportServiceImpl:
@Overridepublic OrderReportVO getOrderReportStatistics(LocalDate begin, LocalDate end) {OrderReportVO orderReportVO=new OrderReportVO();//1.封裝日期數據List<LocalDate> dateList=new ArrayList<>();dateList.add(begin);while(!begin.equals(end)){begin=begin.plusDays(1);dateList.add(begin);}//將集合轉成字符串的同時在每個元素間加一個逗號String dateList1=StringUtils.join(dateList,",");orderReportVO.setDateList(dateList1);//2.訂單總數List<Integer> totalOrder=new ArrayList<>();//3.有效訂單數List<Integer> realOrder=new ArrayList<>();//每天的訂單總數以及有效訂單數for (LocalDate localDate : dateList) {//00:00:00LocalDateTime beginTime=LocalDateTime.of(localDate, LocalTime.MIN);//23:59:59LocalDateTime endTime=LocalDateTime.of(localDate, LocalTime.MAX);Map map=new HashMap();map.put("begin",beginTime);map.put("end",endTime);Integer total=orderMapper.getByMap(map);if(total==null){total=0;}totalOrder.add(total);map.put("status",Orders.COMPLETED);Integer real=orderMapper.getByMap(map);if(real==null){real=0;}realOrder.add(real);}String totalOrder1=StringUtils.join(totalOrder,",");String realOrder1=StringUtils.join(realOrder,",");//計算時間區間內的訂單總數量Integer sum=0;for (Integer integer : totalOrder) {sum+=integer;}//計算時間區間內的有效訂單數量Integer real=0;for (Integer integer : realOrder) {real+=integer;}//計算訂單完成率double orderCompletionRate=0.0;if (sum!=0) {orderCompletionRate= (double) real /sum;}orderReportVO.setOrderCompletionRate(orderCompletionRate);orderReportVO.setOrderCountList(totalOrder1);orderReportVO.setValidOrderCountList(realOrder1);orderReportVO.setTotalOrderCount(sum);orderReportVO.setValidOrderCount(real);System.out.println(orderReportVO);return orderReportVO;}
銷量排名統計
ReportController:
@GetMapping("/top10")@ApiOperation("銷量排名top10")public Result<SalesTop10ReportVO> top10(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin, @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){log.info("銷量排名top10:{},{}",begin,end);SalesTop10ReportVO salesTop10ReportVO=reportService.getTop10(begin,end);return Result.success(salesTop10ReportVO);}
ReportServiceImpl:
public SalesTop10ReportVO getTop10(LocalDate begin, LocalDate end) {SalesTop10ReportVO salesTop10ReportVO=new SalesTop10ReportVO();//00:00:00LocalDateTime beginTime=LocalDateTime.of(begin, LocalTime.MIN);//23:59:59LocalDateTime endTime=LocalDateTime.of(end, LocalTime.MAX);List<GoodsSalesDTO> goodsSalesDTOList=orderMapper.getSalesTop10(beginTime,endTime);//遍歷取出DTO中的numa和number放到對應的集合中去List<String> nameList=new ArrayList<>();List<String> numberList=new ArrayList<>();for (GoodsSalesDTO goodsSalesDTO : goodsSalesDTOList) {nameList.add(goodsSalesDTO.getName());numberList.add(String.valueOf(goodsSalesDTO.getNumber()));}String nameLists=StringUtils.join(nameList,",");String numberLists=StringUtils.join(numberList,",");salesTop10ReportVO.setNameList(nameLists);salesTop10ReportVO.setNumberList(numberLists);return salesTop10ReportVO;}
OrderMapper.xml:
分析一下這里的SQL語句,因為我們要根據訂單狀態(對應orders表中的status)查訂單的名稱以及數量(對應order_details表中的number),所以涉及到聯表查詢。查詢前10,所以只需limit 0,10
<!--統計銷量前10--><select id="getSalesTop10" resultType="com.sky.dto.GoodsSalesDTO">select od.name,sum(od.number) number from order_detail od,orders o where od.id=o.id and o.status=5<if test="begin!=null">and order_time > #{begin}</if><if test="end!=null">and order_time < #{end}</if>group by od.name order by number desc limit 0,10</select>
Day12 數據統計-Excel報表
工作臺
這里課程中的代碼是直接導入的,我還是選擇手敲一遍。
工作臺展示的數據:
- 今日數據
- 訂單管理
- 菜品總覽
- 套餐總覽
- 訂單信息
為了簡便展示,這里我直接給出一個類中的全部代碼了,可以根據注釋理解。
- WorkSpaceController
@RestController
@RequestMapping("/admin/workspace")
@Slf4j
@Api(tags = "工作臺相關接口")
public class WorkSpaceController {@Autowiredprivate WorkSpaceService workSpaceService;//查詢工作臺今日數據@GetMapping("/businessData")@ApiOperation("查詢工作臺今日數據")public Result<BusinessDataVO> businessData(){log.info("查詢工作臺今日數據");//獲取開始時間LocalDateTime beginTime=LocalDateTime.now().with(LocalTime.MIN);//獲取結束時間LocalDateTime endTime=LocalDateTime.now().with(LocalTime.MAX);BusinessDataVO businessDataVO=workSpaceService.getBusinessData(beginTime,endTime);return Result.success(businessDataVO);}//查詢訂單管理數據@GetMapping("/overviewOrders")@ApiOperation("查詢訂單管理數據")public Result<OrderOverViewVO> overViewOrders(){log.info("查詢訂單管理數據");return Result.success(workSpaceService.getOrderOverView());}//查詢菜品總覽@GetMapping("/overviewDishes")@ApiOperation("查詢菜品總覽")public Result<DishOverViewVO> overViewDishes(){return Result.success(workSpaceService.getDishOverView());}//查詢套餐總覽@GetMapping("/overviewSetmeals")@ApiOperation("查詢套餐總覽")public Result<SetmealOverViewVO> overViewSetmeal(){return Result.success(workSpaceService.getSetmealOvermeal());}
}
- WorkSpaceService
public interface WorkSpaceService {BusinessDataVO getBusinessData(LocalDateTime beginTime, LocalDateTime endTime);OrderOverViewVO getOrderOverView();DishOverViewVO getDishOverView();SetmealOverViewVO getSetmealOvermeal();
}
- WorkSpaceServiceImpl(這里很多方法我們都在OrderMapper中已經寫好了,直接調用即可)
@Service
public class WorkSpaceServiceImpl implements WorkSpaceService {@Autowiredprivate OrderMapper orderMapper;@Autowiredprivate UserMapper userMapper;@Autowiredprivate DishMapper dishMapper;@Autowiredprivate SetmealMapper setmealMapper;//查詢工作臺今日數據public BusinessDataVO getBusinessData(LocalDateTime beginTime, LocalDateTime endTime) {BusinessDataVO businessDataVO=new BusinessDataVO();Map map=new HashMap();map.put("begin",beginTime);map.put("end",endTime);//訂單總數Integer total=orderMapper.getByMap(map);map.put("status",5);//營業額Double sum = orderMapper.getSumByMap(map);sum = sum == null? 0.0 : sum;//有效訂單數Integer real=orderMapper.getByMap(map);//平均客單價double average=0.0;//訂單完成率double complete=0.0;if(total!=0&&real!=0){complete= (double) real /total;average=sum/real;}//新增用戶數Integer newUser=userMapper.countByMap(map);businessDataVO.setTurnover(sum);businessDataVO.setNewUsers(newUser);businessDataVO.setOrderCompletionRate(complete);businessDataVO.setValidOrderCount(real);businessDataVO.setUnitPrice(average);return businessDataVO;}//查詢訂單管理數據@Overridepublic OrderOverViewVO getOrderOverView() {OrderOverViewVO orderOverViewVO=new OrderOverViewVO();Map map = new HashMap();map.put("begin", LocalDateTime.now().with(LocalTime.MIN));//待接單map.put("status", Orders.TO_BE_CONFIRMED);Integer status1=orderMapper.getByMap(map);//待派送map.put("status",Orders.CONFIRMED);Integer status2=orderMapper.getByMap(map);//已完成map.put("status",Orders.COMPLETED);Integer status3=orderMapper.getByMap(map);//已取消map.put("status",Orders.CANCELLED);Integer status4=orderMapper.getByMap(map);//全部訂單map.put("status",null);Integer status5=orderMapper.getByMap(map);orderOverViewVO.setWaitingOrders(status1);orderOverViewVO.setDeliveredOrders(status2);orderOverViewVO.setCompletedOrders(status3);orderOverViewVO.setCancelledOrders(status4);orderOverViewVO.setAllOrders(status5);return orderOverViewVO;}//查詢菜品總覽@Overridepublic DishOverViewVO getDishOverView() {DishOverViewVO dishOverViewVO=new DishOverViewVO();Integer on=dishMapper.onStatus();Integer off=dishMapper.offStatus();dishOverViewVO.setSold(on);dishOverViewVO.setDiscontinued(off);return dishOverViewVO;}//查詢套餐總覽@Overridepublic SetmealOverViewVO getSetmealOvermeal() {SetmealOverViewVO setmealOverViewVO=new SetmealOverViewVO();Integer on=setmealMapper.onStatus();Integer off=setmealMapper.offStatus();setmealOverViewVO.setSold(on);setmealOverViewVO.setDiscontinued(off);return setmealOverViewVO;}
}
- DishMapper(這里是SQL語句少我使用這種方法,標準的應該是使用動態SQL)
@Select("select count(id) from dish where status=1")Integer onStatus();@Select("select count(id) from dish where status=0")Integer offStatus();
- SetmealMapper
@Select("select count(id) from setmeal where status=1")Integer onStatus();@Select("select count(id) from setmeal where status=0")Integer offStatus();
Apache POI
簡介:Apache POI可以處理Office的各種文件格式。允許我們使用POI在Java程序中對Office文件進行讀寫操作。一般用于處理Excel文件。
實例:
寫入Excel文件:
//在內存中創建一個excel文件XSSFWorkbook excel=new XSSFWorkbook();//在excel文件中創建一個sheet頁同時指定其名稱為infoXSSFSheet sheet=excel.createSheet("info");//創建行對象,行和列都從0開始,這里我們指定1表示是第二行XSSFRow row= sheet.createRow(1);//創建單元格并寫入內容row.createCell(1).setCellValue("姓名");row.createCell(2).setCellValue("愛好");row= sheet.createRow(2);//創建單元格并寫入內容row.createCell(1).setCellValue("張三");row.createCell(2).setCellValue("籃球");row= sheet.createRow(3);//創建單元格并寫入內容row.createCell(1).setCellValue("李四");row.createCell(2).setCellValue("游泳");//通過輸出流將內存中的Excel文件寫入到磁盤中FileOutputStream out=new FileOutputStream(new File("E:\\Takeout\\info.xlsx"));excel.write(out);out.close();excel.close();
讀取Excel文件:
InputStream in=new FileInputStream(new File("E:\\Takeout\\info.xlsx"));//讀取磁盤上已經存在的Excel文件XSSFWorkbook excel=new XSSFWorkbook(in);//讀取Excel文件中第一個Sheet頁XSSFSheet sheet= excel.getSheetAt(0);//獲取Sheet頁中最后一行的的行號(有內容的最后一行)int lastRowNum=sheet.getLastRowNum();for (int i = 1; i <= lastRowNum; i++) {//獲取某一行XSSFRow row= sheet.getRow(i);//獲取單元格對象String stringCellValue1 = row.getCell(1).getStringCellValue();String stringCellValue2 = row.getCell(2).getStringCellValue();System.out.println(stringCellValue1+" "+stringCellValue2);}excel.close();in.close();
導出Excel報表
業務規則:
- 導出Excel文件形式的報表文件
- 導出進30天的運營數據
實現步驟:
- 設計Excel模板文件
- 查詢近30日的運營數據
- 將查詢到的運營數據寫入模板文件內
- 通過輸出流將Excel文件下載到客戶端瀏覽器
實現:
- ReportController
@GetMapping("/export")@ApiOperation("導出Excel報表")public Result export(HttpServletResponse response){log.info("導出Excel報表");reportService.export(response);return Result.success();}
- ReportServiceImpl
//導出運營數據報表@Overridepublic void export(HttpServletResponse response) {//1.查詢數據庫,獲取近30日的營業數據LocalDate dateBegin=LocalDate.now().minusDays(30);LocalDate dateEnd=LocalDate.now().minusDays(1);//查詢概覽數據BusinessDataVO businessDataVO=workSpaceService.getBusinessData(LocalDateTime.of(dateBegin,LocalTime.MIN),LocalDateTime.of(dateEnd,LocalTime.MAX));//2.通過POI將數據寫入Excel文件中InputStream in=this.getClass().getClassLoader().getResourceAsStream("template/運營數據報表模板.xlsx");try{//基于模板文件創建一個新的Excel文件XSSFWorkbook excel=new XSSFWorkbook(in);//獲取報表文件的Sheet頁XSSFSheet sheet= excel.getSheet("Sheet1");//填充概覽數據-時間sheet.getRow(1).getCell(1).setCellValue("時間:"+dateBegin+"到"+dateEnd);//填充概覽數據其他數據//第四行XSSFRow row= sheet.getRow(3);row.getCell(2).setCellValue(businessDataVO.getTurnover());row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());row.getCell(6).setCellValue(businessDataVO.getNewUsers());//第五行row= sheet.getRow(4);row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());row.getCell(4).setCellValue(businessDataVO.getUnitPrice());for (int i=0;i<30;i++){LocalDate date=dateBegin.plusDays(i);//查詢莫一天的數據BusinessDataVO businessDataVO1=workSpaceService.getBusinessData(LocalDateTime.of(date,LocalTime.MIN),LocalDateTime.of(date,LocalTime.MAX));//獲取某一行并填充數據row= sheet.getRow(7+i);row.getCell(1).setCellValue(businessDataVO1.toString());row.getCell(2).setCellValue(businessDataVO1.getTurnover());row.getCell(3).setCellValue(businessDataVO1.getValidOrderCount());row.getCell(4).setCellValue(businessDataVO1.getOrderCompletionRate());row.getCell(5).setCellValue(businessDataVO1.getUnitPrice());row.getCell(6).setCellValue(businessDataVO1.getNewUsers());}//3.通過輸出流將Excel文件下載到客戶端瀏覽器ServletOutputStream out= response.getOutputStream();excel.write(out);//關閉資源out.close();excel.close();}catch (Exception e){e.printStackTrace();}
這里我們需要注意的一個地方,這里老師沒有加上這個后綴,不加的話我這里會報錯:
蒼穹外賣的學習就到這里啦,完結撒花!!!