From c4aa721a61edb5ff4502d621897c266b98ef3d1c Mon Sep 17 00:00:00 2001
From: baizonghao <1719256278@qq.com>
Date: 星期五, 24 二月 2023 11:18:58 +0800
Subject: [PATCH] 按报警点位统计，按区域统计
---
ycl-platform/src/main/java/com/ycl/mapper/unlawful/UnlawfulMapper.java | 28 +++
ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml | 150 +++++++++++++++++++-
ycl-platform/src/main/java/com/ycl/controller/intelligentPatrol/StatisticsController.java | 62 +++++++-
ycl-platform/src/main/java/com/ycl/service/unlawful/UnlawfulService.java | 16 ++
ycl-platform/src/main/java/com/ycl/service/unlawful/impl/UnlawfulServiceImpl.java | 121 ++++++++++++----
5 files changed, 319 insertions(+), 58 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 467a9b9..f37dedc 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
@@ -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) {
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 380972d..5b5d63e 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
@@ -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();
+
+
+
+
+
}
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 c6963b0..410e527 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
@@ -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();
}
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 9c21b2c..16b8cdc 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
@@ -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);
+ }
}
diff --git a/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml b/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml
index 55a42be..c729847 100644
--- a/ycl-platform/src/main/resources/mapper/unlawful/UnlawfulMapper.xml
+++ b/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>
\ No newline at end of file
--
Gitblit v1.8.0