From bcc6047ef19e2bd7a83c7f6e6abcb2cd1d8107cc Mon Sep 17 00:00:00 2001
From: baizonghao <1719256278@qq.com>
Date: 星期五, 24 二月 2023 17:51:36 +0800
Subject: [PATCH] 按照报警时间统计
---
ycl-platform/src/main/java/com/ycl/dto/statistics/TimeDto.java | 16 ++++
ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java | 8 ++
ycl-platform/src/main/resources/mapper/caseHandler/WritMapper.xml | 13 +-
ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml | 61 +++++++++++++++
ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java | 36 +++++---
ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java | 6 +
ycl-platform/src/main/java/com/ycl/service/unlawful/impl/UnlawfulServiceImpl.java | 50 ++++++++++++
7 files changed, 167 insertions(+), 23 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 f37dedc..86f2ecf 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
@@ -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);
}
diff --git a/ycl-platform/src/main/java/com/ycl/dto/statistics/TimeDto.java b/ycl-platform/src/main/java/com/ycl/dto/statistics/TimeDto.java
new file mode 100644
index 0000000..e05d820
--- /dev/null
+++ b/ycl-platform/src/main/java/com/ycl/dto/statistics/TimeDto.java
@@ -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;
+}
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
index 5b5d63e..d475f48 100644
--- a/ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java
+++ b/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();
+
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
index 410e527..3d3f2f5 100644
--- a/ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java
+++ b/ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java
@@ -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();
}
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
index 16b8cdc..a3e2584 100644
--- 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
@@ -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()) //浜嬩欢鎬绘暟
diff --git a/ycl-platform/src/main/resources/mapper/caseHandler/WritMapper.xml b/ycl-platform/src/main/resources/mapper/caseHandler/WritMapper.xml
index 1de2a20..c7b4f8c 100644
--- a/ycl-platform/src/main/resources/mapper/caseHandler/WritMapper.xml
+++ b/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},'%')
diff --git a/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml b/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml
index c729847..1cfa75b 100644
--- a/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml
+++ b/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>
\ No newline at end of file
--
Gitblit v1.8.0