一、介绍
二、营业额统计
需求分析和设计：
Controller:
Service:
/**
* 营业额统计
* @param begindate
* @param enddate
* @return
* */
@Override
public TurnoverReportVO turnoverStatistics(LocalDate begindate, LocalDate enddate) {
//创建时间集合
List<LocalDate> datelist=new ArrayList<>();
//加入起始时间
datelist.add(begindate);
//循环，直到将起始时间->结束时间期间的每一天都加入到集合中
while(!begindate.equals(enddate)){
begindate = begindate.plusDays(1);
datelist.add(begindate);
}
//集合元素按照 a，b格式转化成字符串
String dateList = StringUtils.join(datelist, ",");
List<Double> turnoverList =new ArrayList<>();
for (LocalDate localDate : datelist) {
//根据日期查询营业额 （状态为已完成的订单的综合）
LocalDateTime begin = LocalDateTime.of(localDate, LocalTime.MIN);//LocalTime.MIN 0点 0分 0秒
LocalDateTime end = LocalDateTime.of(localDate, LocalTime.MAX);//LocalTime.MAX 59:59:5999
Map map=new HashMap();
map.put("begin",begin);
map.put("end",end);
map.put("status", Orders.COMPLETED);
Double turnover =orderMapper.sumByMap(map);
//当天没有营业额则置零
turnover = turnover==null?0.0:turnover;
turnoverList.add(turnover);
}
//集合元素按照 a，b格式转化成字符串
String turnoverlist = StringUtils.join(turnoverList, ",");
return TurnoverReportVO.builder()
.turnoverList(turnoverlist)
.dateList(dateList)
.build();
}
Mapper:
<select id="sumByMap" resultType="java.lang.Double">
select sum(amount) from sky_take_out.orders
<where>
<if test=" begin != null">
and order_time > #{begin}
</if>
<if test=" end != null">
and order_time < #{end}
</if>
<if test="status != null">
and status = #{status}
</if>
</where>
</select>
三、用户统计
需求分析和设计：
Controller:
/**
* 用户统计
* @param begin
* @param end
* @return
* */
@ApiOperation("用户统计")
@GetMapping("/userStatistics")
public Result<UserReportVO> getUserStatistics(
@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){
log.info("用户统计:{},{}",begin,end);
return Result.success(reportService.userStatistics(begin,end));
}
Service:
/**
* 用户统计
* @param begin
* @param end
* @return
* */
@Override
public UserReportVO userStatistics(LocalDate begin, LocalDate end) {
List<LocalDate> datelist=new ArrayList<>();
datelist.add(begin);
while(!begin.equals(end)){
//日期计算，循环到enddate为止
begin = begin.plusDays(1);
datelist.add(begin);
}
//集合元素按照 a，b格式转化成字符串
String dateList = StringUtils.join(datelist, ",");
List<Double> newuserlist=new ArrayList<>();
for (LocalDate localDate : datelist) {
//根据日期查询新增用户 （状态为已完成的订单的综合）
LocalDateTime begindate = LocalDateTime.of(localDate, LocalTime.MIN);//LocalTime.MIN 0点 0分 0秒
LocalDateTime enddate = LocalDateTime.of(localDate, LocalTime.MAX);//LocalTime.MAX 59:59:5999
Map map=new HashMap();
map.put("begin",begindate);
map.put("end",enddate);
Double usercount= orderMapper.sumuserByMap(map);
usercount= usercount==null?0:usercount;
newuserlist.add(usercount);
}
String newuser = StringUtils.join(newuserlist, ",");
List<Double> totalueserlist=new ArrayList<>();
for (LocalDate localDate : datelist) {
//根据日期查询总用户
LocalDateTime enddate = LocalDateTime.of(localDate, LocalTime.MAX);//LocalTime.MAX 59:59:5999
Map map=new HashMap();
map.put("end",enddate);
Double totalusercount= orderMapper.sumuserByMap(map);
totalusercount= totalusercount==null?0:totalusercount;
totalueserlist.add(totalusercount);
}
String totaluser = StringUtils.join(totalueserlist, ",");
return UserReportVO.builder()
.newUserList(newuser)
.totalUserList(totaluser)
.dateList(dateList).build();
}
Mapper:
<select id="sumuserByMap" resultType="java.lang.Double">
select count(id) from sky_take_out.user
<where>
<if test=" begin != null">
and create_time > #{begin}
</if>
<if test=" end != null">
and create_time < #{end}
</if>
</where>
</select>
四、订单统计
需求分析和设计：
Controller：
/**
* 订单统计
* @param begin
* @param end
* @return
* */
@ApiOperation("订单统计")
@GetMapping("/ordersStatistics")
public Result<OrderReportVO> ordersStatistics(
@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){
log.info("订单统计:{},{}",begin,end);
return Result.success(reportService.ordersStatistics(begin,end));
}
Service：
/**
* 订单统计
* @param begin
* @param end
* @return
* */
@Override
public OrderReportVO ordersStatistics(LocalDate begin, LocalDate end) {
List<LocalDate> datelist=new ArrayList<>();
datelist.add(begin);
while(!begin.equals(end)){
//日期计算，循环到enddate为止
begin = begin.plusDays(1);
datelist.add(begin);
}
//集合元素按照 a，b格式转化成字符串
String dateList = StringUtils.join(datelist, ",");
//每日订单数
List<Double> orderslist =new ArrayList<>();
for (LocalDate localDate : datelist) {
LocalDateTime begindate = LocalDateTime.of(localDate, LocalTime.MIN);
LocalDateTime enddate = LocalDateTime.of(localDate, LocalTime.MAX);
Map map=new HashMap();
map.put("begin",begindate);
map.put("end",enddate);
//每日订单数量
Double ordercount = orderMapper.sumorderByMap(map);
orderslist.add(ordercount);
}
//集合元素按照 a，b格式转化成字符串
String orderCountList = StringUtils.join(orderslist, ",");
//每日有效订单数
List<Double> validorderslist =new ArrayList<>();
for (LocalDate localDate : datelist) {
LocalDateTime begindate = LocalDateTime.of(localDate, LocalTime.MIN);
LocalDateTime enddate = LocalDateTime.of(localDate, LocalTime.MAX);
Map map=new HashMap();
map.put("begin",begindate);
map.put("end",enddate);
map.put("status",Orders.COMPLETED);
//每日有效订单数量
Double validordercount = orderMapper.sumorderByMap(map);
validorderslist.add(validordercount);
}
//集合元素按照 a，b格式转化成字符串
String validorderCountList = StringUtils.join(validorderslist, ",");
//完成率
Map map=new HashMap();
map.put("end",LocalDateTime.now());
Double total=orderMapper.sumorderByMap(map);
int totalorder =total.intValue();
Double valid=orderMapper.sumorderByMap(map);
int validordercount=valid.intValue();
Double orderCompletion=valid/total;
return OrderReportVO.builder()
.orderCompletionRate(orderCompletion)
.orderCountList(orderCountList)
.validOrderCountList(validorderCountList)
.totalOrderCount(totalorder)
.validOrderCount(validordercount)
.dateList(dateList).build();
}
Mapper：
<select id="sumorderByMap" resultType="java.lang.Double">
select count(id) from sky_take_out.orders
<where>
<if test=" begin != null">
and order_time > #{begin}
</if>
<if test=" end != null">
and order_time < #{end}
</if>
<if test=" status != null">
and status = #{status}
</if>
</where>
</select>
五、销量排名
需求分析和设计：
Controller:
Service:
/**
* 查询销量top10
* @param begin
* @param end
* @return
* */
@Override
public SalesTop10ReportVO getTop10(LocalDate begin, LocalDate end) {
LocalDateTime begindate = LocalDateTime.of(begin, LocalTime.MIN);
LocalDateTime enddate = LocalDateTime.of(end, LocalTime.MAX);
//创建集合，将查询到的数据封装入对象存入集合
List<GoodsSalesDTO> top10 = orderMapper.getTop10(begindate, enddate);
//创建两个集合，将所需数据分别提取
List<String> namelist=new ArrayList<>();
List<Integer> numberList=new ArrayList<>();
for (GoodsSalesDTO goodsSalesDTO : top10) {
namelist.add(goodsSalesDTO.getName());
numberList.add(goodsSalesDTO.getNumber());
}
String name = StringUtils.join(namelist, ",");
String number = StringUtils.join(numberList, ",");
return SalesTop10ReportVO.builder()
.nameList(name)
.numberList(number).build();
}
Mapper：
<select id="getTop10" resultType="com.sky.dto.GoodsSalesDTO">
select od.name name,sum(od.number) number
from sky_take_out.order_detail od , sky_take_out.orders o
where o.id=od.order_id and o.status=5
<if test=" begin != null">
and o.order_time > #{begin}
</if>
<if test=" end != null">
and o.order_time < #{end}
</if>
group by od.name
order by number desc
limit 0,10
</select>
