From ebf167b950e735f873f3fcdbba276df47ada9834 Mon Sep 17 00:00:00 2001 From: baizonghao <1719256278@qq.com> Date: 星期五, 24 二月 2023 01:13:07 +0800 Subject: [PATCH] 按违规类型统计 --- ycl-platform/src/main/java/com/ycl/dto/statistics/CategoryDto.java | 13 ++ ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java | 15 +++ ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml | 75 +++++++++++++++ ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java | 79 +++++++++------ ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java | 13 ++ ycl-platform/src/main/java/com/ycl/dto/statistics/StatusDto.java | 17 +++ ycl-platform/src/main/java/com/ycl/service/unlawful/impl/UnlawfulServiceImpl.java | 81 ++++++++++++++++ ycl-platform/src/main/java/com/ycl/dto/statistics/UnlawfulDto.java | 2 8 files changed, 265 insertions(+), 30 deletions(-) diff --git a/ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java b/ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java index 084a6b8..467a9b9 100644 --- a/ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java +++ b/ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java @@ -8,15 +8,18 @@ import com.ycl.dto.caseHandler.QueryForViolationParam; import com.ycl.dto.statistics.UnlawfulDto; import com.ycl.dto.statistics.UnlawfulShopDto; +import com.ycl.service.unlawful.UnlawfulService; import com.ycl.utils.EasyExcelUtils; import com.ycl.vo.casePool.QueryForViolationVO; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; +import org.springframework.beans.factory.annotation.Autowired; import org.springframework.format.annotation.DateTimeFormat; import org.springframework.web.bind.annotation.*; import javax.servlet.http.HttpServletResponse; import java.time.LocalDateTime; +import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.Arrays; import java.util.List; @@ -33,23 +36,26 @@ @Api(tags = "杩濈珷-杩濇硶缁熻") public class StatisticsController extends BaseController { - List<UnlawfulDto> ls = new ArrayList<>(Arrays.asList( - new UnlawfulDto("甯傚満鐩戠", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鍏畨", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鑷劧璧勬簮", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鐢熸�佺幆澧�", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鍦版柟绔嬫硶", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鍦伴渿", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鏁欒偛", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("缁忎俊", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鏋椾笟", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鍐滄潙鐜鍗敓", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("姘旇薄", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("浜洪槻", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("姘磋鏀�", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("搴旀�ョ鐞�", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), - new UnlawfulDto("鍏朵粬", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0)) - ); +// List<UnlawfulDto> ls = new ArrayList<>(Arrays.asList( +// new UnlawfulDto("甯傚満鐩戠", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鍏畨", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鑷劧璧勬簮", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鐢熸�佺幆澧�", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鍦版柟绔嬫硶", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鍦伴渿", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鏁欒偛", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("缁忎俊", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鏋椾笟", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鍐滄潙鐜鍗敓", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("姘旇薄", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("浜洪槻", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("姘磋鏀�", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("搴旀�ョ鐞�", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0), +// new UnlawfulDto("鍏朵粬", 100, 0.6, 10, 1, 20, 5, 20, 0.6, 0.9, 0.0)) +// ); + + @Autowired + private UnlawfulService unlawfulService; @GetMapping("/unlawful/type") @ApiOperation("鎸夎繚瑙勭被鍨嬬粺璁�") @@ -58,13 +64,25 @@ @RequestParam(required = true) Integer pageSize, @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime beginTime, @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime endTime) { + DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); + String startTime = null; + String endTime1 = null; + if (beginTime != null){ + startTime = beginTime.format(fmt); + } + if (endTime != null){ + endTime1 = beginTime.format(fmt); + } + IPage<UnlawfulDto> page = new Page<>(); - page.setTotal(ls.size()); - page.setRecords(ls); + List<UnlawfulDto> unlawfulByType = unlawfulService.getUnlawfulByType((currentPage - 1) * pageSize, pageSize, startTime, endTime1); + + page.setTotal(unlawfulByType.size()); + page.setRecords(unlawfulByType); return CommonResult.success(page); } - @GetMapping("/unlawful/point") + /*@GetMapping("/unlawful/point") @ApiOperation("鎸夌偣浣嶇粺璁�") @LogSave(operationType = "鎸夌偣浣嶇粺璁�", contain = "鏌ヨ") public CommonResult<IPage<UnlawfulDto>> searchByPoint(@RequestParam(required = true) Integer currentPage, @@ -75,9 +93,9 @@ page.setTotal(ls.size()); page.setRecords(ls); return CommonResult.success(page); - } + }*/ - @GetMapping("/unlawful/time") + /*@GetMapping("/unlawful/time") @ApiOperation("鎸夋椂闂寸粺璁�") @LogSave(operationType = "鎸夋椂闂寸粺璁�", contain = "鏌ヨ") public CommonResult<IPage<UnlawfulDto>> searchByTime(@RequestParam(required = true) Integer currentPage, @@ -88,9 +106,9 @@ page.setTotal(ls.size()); page.setRecords(ls); return CommonResult.success(page); - } + }*/ - @GetMapping("/unlawful/area") + /*@GetMapping("/unlawful/area") @ApiOperation("鎸夊尯鍩熺粺璁�") @LogSave(operationType = "鎸夊尯鍩熺粺璁�", contain = "鏌ヨ") public CommonResult<IPage<UnlawfulDto>> searchByArea(@RequestParam(required = true) Integer currentPage, @@ -101,9 +119,9 @@ page.setTotal(ls.size()); page.setRecords(ls); return CommonResult.success(page); - } + }*/ - @GetMapping("/unlawful/shop") + /*@GetMapping("/unlawful/shop") @ApiOperation("闂ㄥ墠涓夊寘缁熻") @LogSave(operationType = "闂ㄥ墠涓夊寘缁熻", contain = "鏌ヨ") public CommonResult<IPage<UnlawfulShopDto>> searchByShop(@RequestParam(required = true) Integer currentPage, @@ -128,16 +146,17 @@ page.setTotal(ls.size()); page.setRecords(ls); return CommonResult.success(page); - } + }*/ @PostMapping("/export/unlawful") @ApiOperation("鎸夊悇绉嶇粺璁℃柟寮�-瀵煎嚭") public void export(HttpServletResponse response) { + List<UnlawfulDto> unlawfulByTypeExport = unlawfulService.getUnlawfulByTypeExport(); String sheetName = "鎸夌粺璁℃柟寮�"; - EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, ls); + EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, unlawfulByTypeExport); } - @PostMapping("/export/shop") + /*@PostMapping("/export/shop") @ApiOperation("闂ㄥ墠涓夊寘-瀵煎嚭") public void exportShop(HttpServletResponse response) { List<UnlawfulShopDto> ls = new ArrayList<>(); @@ -156,5 +175,5 @@ ls.add(new UnlawfulShopDto("搴楅摵13", 100, 10, 1, 20, 5, 20, 0.9)); String sheetName = "闂ㄥ墠涓夊寘"; EasyExcelUtils.export(response, sheetName, UnlawfulShopDto.class, ls); - } + }*/ } diff --git a/ycl-platform/src/main/java/com/ycl/dto/statistics/CategoryDto.java b/ycl-platform/src/main/java/com/ycl/dto/statistics/CategoryDto.java new file mode 100644 index 0000000..7399ae1 --- /dev/null +++ b/ycl-platform/src/main/java/com/ycl/dto/statistics/CategoryDto.java @@ -0,0 +1,13 @@ +package com.ycl.dto.statistics; + +import lombok.AllArgsConstructor; +import lombok.Data; +import lombok.NoArgsConstructor; + +@Data +@AllArgsConstructor +@NoArgsConstructor +public class CategoryDto { + Integer id; + String name; +} diff --git a/ycl-platform/src/main/java/com/ycl/dto/statistics/StatusDto.java b/ycl-platform/src/main/java/com/ycl/dto/statistics/StatusDto.java new file mode 100644 index 0000000..5e8b7f2 --- /dev/null +++ b/ycl-platform/src/main/java/com/ycl/dto/statistics/StatusDto.java @@ -0,0 +1,17 @@ +package com.ycl.dto.statistics; + +import lombok.AllArgsConstructor; +import lombok.Data; +import lombok.NoArgsConstructor; + +@Data +@AllArgsConstructor +@NoArgsConstructor +public class StatusDto { + Integer total; + Integer register; + Integer notRegister; + Integer closing; + Integer relearn; + Integer checked; +} diff --git a/ycl-platform/src/main/java/com/ycl/dto/statistics/UnlawfulDto.java b/ycl-platform/src/main/java/com/ycl/dto/statistics/UnlawfulDto.java index bcd8e99..02c4ec5 100644 --- a/ycl-platform/src/main/java/com/ycl/dto/statistics/UnlawfulDto.java +++ b/ycl-platform/src/main/java/com/ycl/dto/statistics/UnlawfulDto.java @@ -2,6 +2,7 @@ import com.alibaba.excel.annotation.ExcelProperty; import lombok.AllArgsConstructor; +import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; @@ -16,6 +17,7 @@ @Data @AllArgsConstructor @NoArgsConstructor +@Builder public class UnlawfulDto { /** diff --git a/ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java b/ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java new file mode 100644 index 0000000..380972d --- /dev/null +++ b/ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java @@ -0,0 +1,15 @@ +package com.ycl.mapper.unlawful; + +import com.ycl.dto.statistics.CategoryDto; +import com.ycl.dto.statistics.StatusDto; + +import java.util.List; + +public interface UnlawfulMapper { + List<CategoryDto> getDataByType(Integer currentPage, Integer pageSize, String startTime, String endTime); + StatusDto getStatusDataByType(String startTime, String endTime, Integer dictionaryId); + + List<CategoryDto> getDataByTypeExp(); + + Integer getTotal(); +} diff --git a/ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java b/ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java new file mode 100644 index 0000000..c6963b0 --- /dev/null +++ b/ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java @@ -0,0 +1,13 @@ +package com.ycl.service.unlawful; + +import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.ycl.dto.statistics.UnlawfulDto; + +import java.util.List; + +public interface UnlawfulService { + + public List<UnlawfulDto> getUnlawfulByType(Integer currentPage, Integer pageSize, String startTime, String endTime); + + public List<UnlawfulDto> getUnlawfulByTypeExport(); +} diff --git a/ycl-platform/src/main/java/com/ycl/service/unlawful/impl/UnlawfulServiceImpl.java b/ycl-platform/src/main/java/com/ycl/service/unlawful/impl/UnlawfulServiceImpl.java new file mode 100644 index 0000000..9c21b2c --- /dev/null +++ b/ycl-platform/src/main/java/com/ycl/service/unlawful/impl/UnlawfulServiceImpl.java @@ -0,0 +1,81 @@ +package com.ycl.service.unlawful.impl; + +import com.ycl.dto.statistics.CategoryDto; +import com.ycl.dto.statistics.StatusDto; +import com.ycl.dto.statistics.UnlawfulDto; +import com.ycl.mapper.unlawful.UnlawfulMapper; +import com.ycl.service.unlawful.UnlawfulService; +import org.springframework.stereotype.Service; + +import javax.annotation.Resource; +import java.math.RoundingMode; +import java.text.NumberFormat; +import java.util.ArrayList; +import java.util.List; +import java.util.function.Consumer; + +@Service +public class UnlawfulServiceImpl implements UnlawfulService { + + @Resource + private UnlawfulMapper unlawfuldao; + + + @Override + public List<UnlawfulDto> getUnlawfulByType(Integer currentPage, Integer pageSize, String startTime, String endTime) { + List<UnlawfulDto> res = new ArrayList<>(); + Double total = unlawfuldao.getTotal().doubleValue(); + List<CategoryDto> data = unlawfuldao.getDataByType(currentPage, pageSize, startTime, endTime); + data.forEach(categoryDto -> { + Double checkedRatio; //瀹℃牳鐜� + Double registerRatio; //绔嬫鐜� + StatusDto statusData = unlawfuldao.getStatusDataByType(startTime, endTime, categoryDto.getId()); + UnlawfulDto build = new UnlawfulDto().builder().name(categoryDto.getName()) //绫诲瀷鍚嶇О + .count(statusData.getTotal()) //浜嬩欢鎬绘暟 + .ratio(changeFormat(statusData.getTotal().doubleValue() / total)) //鍗犳瘮 + .register(statusData.getRegister()) //绔嬫 + .notRegister(statusData.getNotRegister()) //鏆備笉绔嬫 + .closing(statusData.getClosing()) //缁撴 + .relearn(statusData.getRelearn()) //鍦ㄥ涔� + .checked(statusData.getChecked()) //宸插鏍� + .checkedRatio(changeFormat(statusData.getChecked().doubleValue() / statusData.getTotal().doubleValue())) //瀹℃牳鐜� + .registerRatio(changeFormat(statusData.getRegister().doubleValue() / statusData.getTotal().doubleValue())) //绔嬫鐜� + .build(); + res.add(build); + }); + return res; + } + + @Override + public List<UnlawfulDto> getUnlawfulByTypeExport() { + List<UnlawfulDto> res = new ArrayList<>(); + Double total = unlawfuldao.getTotal().doubleValue(); + List<CategoryDto> data = unlawfuldao.getDataByTypeExp(); + data.forEach(categoryDto -> { + Double checkedRatio; //瀹℃牳鐜� + Double registerRatio; //绔嬫鐜� + StatusDto statusData = unlawfuldao.getStatusDataByType(null, null, categoryDto.getId()); + UnlawfulDto build = new UnlawfulDto().builder().name(categoryDto.getName()) //绫诲瀷鍚嶇О + .count(statusData.getTotal()) //浜嬩欢鎬绘暟 + .ratio(changeFormat(statusData.getTotal().doubleValue() / total)) //鍗犳瘮 + .register(statusData.getRegister()) //绔嬫 + .notRegister(statusData.getNotRegister()) //鏆備笉绔嬫 + .closing(statusData.getClosing()) //缁撴 + .relearn(statusData.getRelearn()) //鍦ㄥ涔� + .checked(statusData.getChecked()) //宸插鏍� + .checkedRatio(changeFormat(statusData.getChecked().doubleValue() / statusData.getTotal().doubleValue())) //瀹℃牳鐜� + .registerRatio(changeFormat(statusData.getRegister().doubleValue() / statusData.getTotal().doubleValue())) //绔嬫鐜� + .build(); + res.add(build); + }); + return res; + } + + private Double changeFormat(Double previous){ + NumberFormat numberInstance = NumberFormat.getNumberInstance(); + numberInstance.setMaximumFractionDigits(2); + numberInstance.setRoundingMode(RoundingMode.HALF_UP); + String format = numberInstance.format(previous); + return Double.parseDouble(format); + } +} diff --git a/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml b/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml new file mode 100644 index 0000000..55a42be --- /dev/null +++ b/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml @@ -0,0 +1,75 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> +<mapper namespace="com.ycl.mapper.unlawful.UnlawfulMapper"> + <select id="getDataByType" resultType="com.ycl.dto.statistics.CategoryDto"> + SELECT + t4.id id, + t4.NAME name + FROM + `ums_base_case` AS ubc + JOIN ums_violations AS uv ON ubc.id = uv.id + LEFT JOIN ums_data_dictionary AS t3 ON uv.category_id = t3.id + LEFT JOIN ums_data_dictionary AS t4 ON uv.type_id = t4.id + LEFT JOIN ums_sccg_region t5 ON ubc.street_id = t5.id + WHERE + ubc.category =1 + and t4.`name` is NOT NULL + <if test="startTime !='' and endTime !='' and startTime!=null and endTime !=null"> + and ubc.create_time between #{startTime} and #{endTime} + </if> + group by + t4.id + limit #{currentPage}, #{pageSize} + </select> + <select id="getStatusDataByType" resultType="com.ycl.dto.statistics.StatusDto"> + SELECT + count(1) total, + sum(case when ubc.state=5 then 1 else 0 end) register, + sum(case when ubc.state=4 then 1 else 0 end ) notRegister, + sum(case when ubc.state=9 then 1 else 0 end ) closing, + sum(case when ubc.state=3 then 1 else 0 end ) relearn, + sum(case when ubc.state=8 then 1 else 0 end ) checked + FROM + `ums_base_case` AS ubc + JOIN ums_violations AS uv ON ubc.id = uv.id + LEFT JOIN ums_data_dictionary AS t3 ON uv.category_id = t3.id + LEFT JOIN ums_data_dictionary AS t4 ON uv.type_id = t4.id + LEFT JOIN ums_sccg_region t5 ON ubc.street_id = t5.id + WHERE + ubc.category =1 + and t4.`id` = #{dictionaryId} + <if test="startTime !='' and endTime !='' and startTime!=null and endTime !=null"> + and ubc.create_time between #{startTime} and #{endTime} + </if> + </select> + <select id="getTotal" resultType="java.lang.Integer"> + SELECT + count(*) + FROM + `ums_base_case` AS ubc + JOIN ums_violations AS uv ON ubc.id = uv.id + LEFT JOIN ums_data_dictionary AS t3 ON uv.category_id = t3.id + LEFT JOIN ums_data_dictionary AS t4 ON uv.type_id = t4.id + LEFT JOIN ums_sccg_region t5 ON ubc.street_id = t5.id + WHERE + ubc.category = 1 + AND t4.`name` IS NOT NULL + + </select> + <select id="getDataByTypeExp" resultType="com.ycl.dto.statistics.CategoryDto"> + SELECT + t4.id id, + t4.NAME name + FROM + `ums_base_case` AS ubc + JOIN ums_violations AS uv ON ubc.id = uv.id + LEFT JOIN ums_data_dictionary AS t3 ON uv.category_id = t3.id + LEFT JOIN ums_data_dictionary AS t4 ON uv.type_id = t4.id + LEFT JOIN ums_sccg_region t5 ON ubc.street_id = t5.id + WHERE + ubc.category =1 + and t4.`name` is NOT NULL + group by + t4.id + </select> +</mapper> \ No newline at end of file -- Gitblit v1.8.0