ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java
@@ -82,18 +82,29 @@ return CommonResult.success(page); } /*@GetMapping("/unlawful/point") @GetMapping("/unlawful/point") @ApiOperation("按点位统计") @LogSave(operationType = "按点位统计", contain = "查询") public CommonResult<IPage<UnlawfulDto>> searchByPoint(@RequestParam(required = true) Integer currentPage, @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.getUnlawfulBySite((currentPage - 1) * pageSize, pageSize, startTime, endTime1); page.setTotal(unlawfulByType.size()); page.setRecords(unlawfulByType); return CommonResult.success(page); }*/ } /*@GetMapping("/unlawful/time") @ApiOperation("按时间统计") @@ -108,18 +119,29 @@ return CommonResult.success(page); }*/ /*@GetMapping("/unlawful/area") @GetMapping("/unlawful/area") @ApiOperation("按区域统计") @LogSave(operationType = "按区域统计", contain = "查询") public CommonResult<IPage<UnlawfulDto>> searchByArea(@RequestParam(required = true) Integer currentPage, @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.getUnlawfulBySite((currentPage - 1) * pageSize, pageSize, startTime, endTime1); page.setTotal(unlawfulByType.size()); page.setRecords(unlawfulByType); return CommonResult.success(page); }*/ } /*@GetMapping("/unlawful/shop") @ApiOperation("门前三包统计") @@ -156,6 +178,30 @@ EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, unlawfulByTypeExport); } @PostMapping("/export/unlawful/type") @ApiOperation("按各种统计方式-导出") public void exportType(HttpServletResponse response) { List<UnlawfulDto> unlawfulByTypeExport = unlawfulService.getUnlawfulByTypeExport(); String sheetName = "按统计方式"; EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, unlawfulByTypeExport); } @PostMapping("/export/unlawful/street") @ApiOperation("按各种统计方式-导出") public void exportStreet(HttpServletResponse response) { List<UnlawfulDto> unlawfulByStreetExport = unlawfulService.getUnlawfulByStreetExport(); String sheetName = "按统计方式"; EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, unlawfulByStreetExport); } @PostMapping("/export/unlawful/site") @ApiOperation("按各种统计方式-导出") public void exportSite(HttpServletResponse response) { List<UnlawfulDto> unlawfulBySiteExport = unlawfulService.getUnlawfulBySiteExport(); String sheetName = "按统计方式"; EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, unlawfulBySiteExport); } /*@PostMapping("/export/shop") @ApiOperation("门前三包-导出") public void exportShop(HttpServletResponse response) { ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java
@@ -6,10 +6,34 @@ import java.util.List; public interface UnlawfulMapper { /** * 获取总违规数量 */ Integer getTotal(); /** * 按照违规类型统计 */ List<CategoryDto> getDataByType(Integer currentPage, Integer pageSize, String startTime, String endTime); StatusDto getStatusDataByType(String startTime, String endTime, Integer dictionaryId); List<CategoryDto> getDataByTypeExp(); Integer getTotal(); /** * 按照区域统计 */ List<CategoryDto> getDataByStreet(Integer currentPage, Integer pageSize, String startTime, String endTime); StatusDto getStatusDataByStreet(String startTime, String endTime, Integer streetId); List<CategoryDto> getDataByStreetExp(); /** * 按点位统计 */ List<String> getDataBySite(Integer currentPage, Integer pageSize, String startTime, String endTime); StatusDto getStatusDataBySite(String startTime, String endTime, String site); List<String> getDataBySiteExp(); } ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java
@@ -7,7 +7,21 @@ public interface UnlawfulService { /** * 按类型 */ public List<UnlawfulDto> getUnlawfulByType(Integer currentPage, Integer pageSize, String startTime, String endTime); public List<UnlawfulDto> getUnlawfulByTypeExport(); /** * 按区域 */ public List<UnlawfulDto> getUnlawfulByStreet(Integer currentPage, Integer pageSize, String startTime, String endTime); public List<UnlawfulDto> getUnlawfulByStreetExport(); /** * 按报警点位 */ public List<UnlawfulDto> getUnlawfulBySite(Integer currentPage, Integer pageSize, String startTime, String endTime); public List<UnlawfulDto> getUnlawfulBySiteExport(); } ycl-platform/src/main/java/com/ycl/service/unlawful/impl/UnlawfulServiceImpl.java
@@ -12,61 +12,84 @@ import java.text.NumberFormat; import java.util.ArrayList; import java.util.List; import java.util.function.Consumer; @Service public class UnlawfulServiceImpl implements UnlawfulService { private List<UnlawfulDto> res = new ArrayList<>(); @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); format(res, total, categoryDto, statusData); }); return res; } @Override public List<UnlawfulDto> getUnlawfulByTypeExport() { Double total = unlawfuldao.getTotal().doubleValue(); List<CategoryDto> data = unlawfuldao.getDataByTypeExp(); data.forEach(categoryDto -> { StatusDto statusData = unlawfuldao.getStatusDataByType(null, null, categoryDto.getId()); format(res, total, categoryDto, statusData); }); return res; } /** * 按区域 */ @Override public List<UnlawfulDto> getUnlawfulByTypeExport() { List<UnlawfulDto> res = new ArrayList<>(); public List<UnlawfulDto> getUnlawfulByStreet(Integer currentPage, Integer pageSize, String startTime, String endTime) { Double total = unlawfuldao.getTotal().doubleValue(); List<CategoryDto> data = unlawfuldao.getDataByTypeExp(); List<CategoryDto> data = unlawfuldao.getDataByStreet(currentPage, pageSize, startTime, endTime); 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); StatusDto statusData = unlawfuldao.getStatusDataByStreet(startTime, endTime, categoryDto.getId()); format(res, total, categoryDto, statusData); }); return res; } @Override public List<UnlawfulDto> getUnlawfulByStreetExport() { Double total = unlawfuldao.getTotal().doubleValue(); List<CategoryDto> data = unlawfuldao.getDataByStreetExp(); data.forEach(categoryDto -> { StatusDto statusData = unlawfuldao.getStatusDataByStreet(null, null, categoryDto.getId()); format(res, total, categoryDto, statusData); }); return res; } /** * 按报警点位 */ @Override public List<UnlawfulDto> getUnlawfulBySite(Integer currentPage, Integer pageSize, String startTime, String endTime) { Double total = unlawfuldao.getTotal().doubleValue(); List<String> data = unlawfuldao.getDataBySite(currentPage, pageSize, startTime, endTime); data.forEach(site -> { StatusDto statusData = unlawfuldao.getStatusDataBySite(startTime, endTime, site); format1(res, total, site, statusData); }); return res; } @Override public List<UnlawfulDto> getUnlawfulBySiteExport() { Double total = unlawfuldao.getTotal().doubleValue(); List<String> data = unlawfuldao.getDataBySiteExp(); data.forEach(site -> { StatusDto statusData = unlawfuldao.getStatusDataBySite(null, null, site); format1(res, total, site, statusData); }); return res; } @@ -78,4 +101,34 @@ String format = numberInstance.format(previous); return Double.parseDouble(format); } private void format(List<UnlawfulDto> res, Double total, CategoryDto categoryDto, StatusDto statusData) { UnlawfulDto build = 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); } private void format1(List<UnlawfulDto> res, Double total, String site, StatusDto statusData) { UnlawfulDto build = UnlawfulDto.builder().name(site) //类型名称 .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); } } ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml
@@ -1,6 +1,22 @@ <?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="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="getDataByType" resultType="com.ycl.dto.statistics.CategoryDto"> SELECT t4.id id, @@ -38,24 +54,12 @@ WHERE ubc.category =1 and t4.`id` = #{dictionaryId} 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> </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, @@ -72,4 +76,124 @@ group by t4.id </select> <!-- 按照区域统计--> <select id="getDataByStreet" resultType="com.ycl.dto.statistics.CategoryDto"> SELECT ubc.community_id id, t5.region_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 ubc.community_id limit #{currentPage}, #{pageSize} </select> <select id="getStatusDataByStreet" 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 ubc.`community_id` = #{streetId} 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> </select> <select id="getDataByStreetExp" resultType="com.ycl.dto.statistics.CategoryDto"> SELECT ubc.community_id id, t5.region_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 ubc.community_id </select> <!-- 按点位统计--> <select id="getDataBySite" resultType="java.lang.String"> SELECT ubc.site 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 ubc.site limit #{currentPage}, #{pageSize} </select> <select id="getStatusDataBySite" 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.`name` IS NOT NULL AND ubc.site = #{site} </select> <select id="getDataBySiteExp" resultType="java.lang.String"> SELECT ubc.site 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 ubc.site </select> </mapper>
