ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java
@@ -5,12 +5,9 @@ import com.ycl.annotation.LogSave; import com.ycl.api.CommonResult; import com.ycl.controller.BaseController; 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; @@ -20,8 +17,6 @@ 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; /** @@ -106,18 +101,29 @@ return CommonResult.success(page); } /*@GetMapping("/unlawful/time") @GetMapping("/unlawful/time") @ApiOperation("按时间统计") @LogSave(operationType = "按时间统计", contain = "查询") public CommonResult<IPage<UnlawfulDto>> searchByTime(@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.getUnlawfulByTime((currentPage - 1) * pageSize, pageSize, startTime, endTime1); page.setTotal(unlawfulByType.size()); page.setRecords(unlawfulByType); return CommonResult.success(page); }*/ } @GetMapping("/unlawful/area") @ApiOperation("按区域统计") @@ -179,26 +185,26 @@ } @PostMapping("/export/unlawful/type") @ApiOperation("按各种统计方式-导出") @ApiOperation("按类型统计方式-导出") public void exportType(HttpServletResponse response) { List<UnlawfulDto> unlawfulByTypeExport = unlawfulService.getUnlawfulByTypeExport(); String sheetName = "按统计方式"; String sheetName = "按类型统计方式"; EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, unlawfulByTypeExport); } @PostMapping("/export/unlawful/street") @ApiOperation("按各种统计方式-导出") @ApiOperation("按区域统计方式-导出") public void exportStreet(HttpServletResponse response) { List<UnlawfulDto> unlawfulByStreetExport = unlawfulService.getUnlawfulByStreetExport(); String sheetName = "按统计方式"; String sheetName = "按区域统计方式"; EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, unlawfulByStreetExport); } @PostMapping("/export/unlawful/site") @ApiOperation("按各种统计方式-导出") @ApiOperation("按报警点位统计方式-导出") public void exportSite(HttpServletResponse response) { List<UnlawfulDto> unlawfulBySiteExport = unlawfulService.getUnlawfulBySiteExport(); String sheetName = "按统计方式"; String sheetName = "按报警点位统计方式"; EasyExcelUtils.export(response, sheetName, UnlawfulDto.class, unlawfulBySiteExport); }
New file @@ -0,0 +1,16 @@ package com.ycl.dto.statistics; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.time.LocalDateTime; import java.util.Date; @Data @AllArgsConstructor @NoArgsConstructor public class TimeDto { Integer id; LocalDateTime time; } ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java
@@ -2,6 +2,7 @@ import com.ycl.dto.statistics.CategoryDto; import com.ycl.dto.statistics.StatusDto; import com.ycl.dto.statistics.TimeDto; import java.util.List; @@ -32,6 +33,13 @@ StatusDto getStatusDataBySite(String startTime, String endTime, String site); List<String> getDataBySiteExp(); /** * 按报警时间 */ List<TimeDto> getDataByTime(Integer currentPage, Integer pageSize, String startTime, String endTime); StatusDto getStatusDataByTime(String startTime, String endTime, Integer id); List<TimeDto> getDataByTimeExp();
@@ -24,4 +24,10 @@ */ public List<UnlawfulDto> getUnlawfulBySite(Integer currentPage, Integer pageSize, String startTime, String endTime); public List<UnlawfulDto> getUnlawfulBySiteExport(); /** * 按报警时间 */ public List<UnlawfulDto> getUnlawfulByTime(Integer currentPage, Integer pageSize, String startTime, String endTime); public List<UnlawfulDto> getUnlawfulByTimeExport(); } ycl-platform/src/main/java/com/ycl/service/unlawful/impl/UnlawfulServiceImpl.java
@@ -2,6 +2,7 @@ import com.ycl.dto.statistics.CategoryDto; import com.ycl.dto.statistics.StatusDto; import com.ycl.dto.statistics.TimeDto; import com.ycl.dto.statistics.UnlawfulDto; import com.ycl.mapper.unlawful.UnlawfulMapper; import com.ycl.service.unlawful.UnlawfulService; @@ -10,13 +11,12 @@ import javax.annotation.Resource; import java.math.RoundingMode; import java.text.NumberFormat; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.List; @Service public class UnlawfulServiceImpl implements UnlawfulService { private List<UnlawfulDto> res = new ArrayList<>(); @Resource private UnlawfulMapper unlawfuldao; @@ -27,6 +27,7 @@ */ @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 -> { @@ -37,6 +38,7 @@ } @Override public List<UnlawfulDto> getUnlawfulByTypeExport() { List<UnlawfulDto> res = new ArrayList<>(); Double total = unlawfuldao.getTotal().doubleValue(); List<CategoryDto> data = unlawfuldao.getDataByTypeExp(); data.forEach(categoryDto -> { @@ -51,6 +53,7 @@ */ @Override public List<UnlawfulDto> getUnlawfulByStreet(Integer currentPage, Integer pageSize, String startTime, String endTime) { List<UnlawfulDto> res = new ArrayList<>(); Double total = unlawfuldao.getTotal().doubleValue(); List<CategoryDto> data = unlawfuldao.getDataByStreet(currentPage, pageSize, startTime, endTime); data.forEach(categoryDto -> { @@ -61,6 +64,7 @@ } @Override public List<UnlawfulDto> getUnlawfulByStreetExport() { List<UnlawfulDto> res = new ArrayList<>(); Double total = unlawfuldao.getTotal().doubleValue(); List<CategoryDto> data = unlawfuldao.getDataByStreetExp(); data.forEach(categoryDto -> { @@ -75,6 +79,7 @@ */ @Override public List<UnlawfulDto> getUnlawfulBySite(Integer currentPage, Integer pageSize, String startTime, String endTime) { List<UnlawfulDto> res = new ArrayList<>(); Double total = unlawfuldao.getTotal().doubleValue(); List<String> data = unlawfuldao.getDataBySite(currentPage, pageSize, startTime, endTime); data.forEach(site -> { @@ -85,11 +90,36 @@ } @Override public List<UnlawfulDto> getUnlawfulBySiteExport() { List<UnlawfulDto> res = new ArrayList<>(); 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; } @Override public List<UnlawfulDto> getUnlawfulByTime(Integer currentPage, Integer pageSize, String startTime, String endTime) { List<UnlawfulDto> res = new ArrayList<>(); Double total = unlawfuldao.getTotal().doubleValue(); List<TimeDto> data = unlawfuldao.getDataByTime(currentPage, pageSize, startTime, endTime); data.forEach(timeDto -> { StatusDto statusData = unlawfuldao.getStatusDataByTime(startTime, endTime, timeDto.getId()); format2(res, total, timeDto, statusData); }); return res; } @Override public List<UnlawfulDto> getUnlawfulByTimeExport() { List<UnlawfulDto> res = new ArrayList<>(); Double total = unlawfuldao.getTotal().doubleValue(); List<TimeDto> data = unlawfuldao.getDataByTimeExp(); data.forEach(timeDto -> { StatusDto statusData = unlawfuldao.getStatusDataByTime(null, null, timeDto.getId()); format2(res, total, timeDto, statusData); }); return res; } @@ -117,6 +147,22 @@ res.add(build); } private void format2(List<UnlawfulDto> res, Double total, TimeDto timeDto, StatusDto statusData) { DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); UnlawfulDto build = UnlawfulDto.builder().name(timeDto.getTime().format(fmt)) //类型名称 .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()) //事件总数 ycl-platform/src/main/resources/mapper/caseHandler/WritMapper.xml
@@ -35,12 +35,12 @@ base_case_id , illegal_building_id, writ_type, writ_code, illegal_type, send_time, limit_time, send_content, rectify_time, rectify_situation, remark, writ_pic, original_pic, rectified_pic, othen_pic, create_user, create_time </sql> <!-- <update id="deleteValueByCaseId">--> <!-- update ums_writ set value = null where base_case_id = #{baseCaseId}--> <!-- </update>--> <delete id="deleteValueByCaseId"> delete from ums_writ where base_case_id = #{baseCaseId} </delete> <update id="deleteValueByCaseId"> update ums_writ set value = null where base_case_id = #{baseCaseId} </update> <!-- <delete id="deleteValueByCaseId">--> <!-- delete from ums_writ where base_case_id = #{baseCaseId}--> <!-- </delete>--> <select id="selectWritPage" resultMap="VoMap"> SELECT w.*, wt.`name` as writ_type_name,bc.`code` event_code,dd.`name` category_name,wt.code template_code FROM ums_writ w @@ -49,6 +49,7 @@ LEFT JOIN ums_data_dictionary dd on ib.category_id = dd.id LEFT JOIN ums_base_case bc on ib.base_case_id = bc.id <where> w.value is not null <if test="keyword != null"> w.writ_code like CONCAT('%',#{keyword},'%') or bc.`code` like CONCAT('%',#{keyword},'%') ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml
@@ -142,6 +142,7 @@ <!-- 按点位统计--> <select id="getDataBySite" resultType="java.lang.String"> SELECT @@ -196,4 +197,64 @@ GROUP BY ubc.site </select> <!-- 按照报警时间--> <select id="getDataByTime" resultType="com.ycl.dto.statistics.TimeDto"> SELECT ubc.id id, ubc.alarm_time time 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.alarm_time between #{startTime} and #{endTime} </if> GROUP BY ubc.alarm_time limit #{currentPage}, #{pageSize} </select> <select id="getStatusDataByTime" 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.id = #{id} </select> <select id="getDataByTimeExp" resultType="com.ycl.dto.statistics.TimeDto"> SELECT ubc.id id, ubc.alarm_time time, 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.alarm_time </select> </mapper>
