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