From 22ad608c105f7ca813b605149a51218059dc251b Mon Sep 17 00:00:00 2001
From: zhongrj <646384940@qq.com>
Date: Mon, 22 Jan 2024 08:45:42 +0800
Subject: [PATCH] 网格查询修改
---
src/main/java/org/springblade/modules/place/mapper/PlaceExtMapper.xml | 6 -
src/main/java/org/springblade/modules/system/mapper/RegionMapper.java | 7 ++
src/main/java/org/springblade/modules/place/service/impl/PlaceServiceImpl.java | 9 ++
src/main/java/org/springblade/modules/grid/mapper/GridWorkLogMapper.xml | 12 ++--
src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml | 5 -
src/main/java/org/springblade/modules/grid/entity/GridmanEntity.java | 5 +
src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml | 5 -
src/main/java/org/springblade/modules/place/mapper/PlaceMapper.xml | 13 +++-
src/main/java/org/springblade/modules/place/mapper/PlaceMapper.java | 3 +
src/main/java/org/springblade/modules/house/service/impl/HouseServiceImpl.java | 8 --
src/main/java/org/springblade/modules/system/mapper/RegionMapper.xml | 21 +++++++
src/main/java/org/springblade/modules/grid/mapper/GridmanMapper.xml | 38 ++++++------
src/main/java/org/springblade/modules/grid/mapper/GridMapper.xml | 3
src/main/java/org/springblade/modules/system/service/impl/RegionServiceImpl.java | 4
src/main/java/org/springblade/common/cache/SysCache.java | 4
15 files changed, 90 insertions(+), 53 deletions(-)
diff --git a/src/main/java/org/springblade/common/cache/SysCache.java b/src/main/java/org/springblade/common/cache/SysCache.java
index c98fa56..377fb23 100644
--- a/src/main/java/org/springblade/common/cache/SysCache.java
+++ b/src/main/java/org/springblade/common/cache/SysCache.java
@@ -345,14 +345,14 @@
return null;
}
List<String> regionCodeList = CacheUtil.get(SYS_CACHE, REGION_CHILDCODES_CODE, regionCode, List.class);
- if (regionCodeList == null) {
+ if (regionCodeList == null || regionCodeList.size()==0) {
regionCodeList = new ArrayList<>();
List<Region> deptChild = getRegionChild(regionCode);
if (deptChild != null) {
List<String> collect = deptChild.stream().map(Region::getCode).collect(Collectors.toList());
regionCodeList.addAll(collect);
}
-// regionCodeList.add(regionCode);
+ regionCodeList.add(regionCode);
CacheUtil.put(SYS_CACHE, REGION_CHILDCODES_CODE, regionCode, regionCodeList);
}
return regionCodeList;
diff --git a/src/main/java/org/springblade/modules/grid/entity/GridmanEntity.java b/src/main/java/org/springblade/modules/grid/entity/GridmanEntity.java
index 02e892e..994c105 100644
--- a/src/main/java/org/springblade/modules/grid/entity/GridmanEntity.java
+++ b/src/main/java/org/springblade/modules/grid/entity/GridmanEntity.java
@@ -55,6 +55,11 @@
@ApiModelProperty(value = "网格id")
private Integer gridId;
/**
+ * 网格编号
+ */
+ @ApiModelProperty(value = "网格编号")
+ private String gridCode;
+ /**
* 关联用户表id
*/
@ApiModelProperty(value = "关联用户表id")
diff --git a/src/main/java/org/springblade/modules/grid/mapper/GridMapper.xml b/src/main/java/org/springblade/modules/grid/mapper/GridMapper.xml
index a584db6..45fb583 100644
--- a/src/main/java/org/springblade/modules/grid/mapper/GridMapper.xml
+++ b/src/main/java/org/springblade/modules/grid/mapper/GridMapper.xml
@@ -82,8 +82,7 @@
bu.phone AS gridPhone
FROM
jczz_grid jg
- LEFT JOIN jczz_gridman jgm on jg.id = jgm.grid_id
- LEFT JOIN jczz_grid_range jgr ON jg.id = jgr.grid_id
+ LEFT JOIN jczz_gridman jgm on jg.grid_code = jgm.grid_code
LEFT JOIN blade_region br on br.code = jg.community_code
LEFT JOIN jczz_place_rel jpr ON locate( jpr.community_name, br.name )> 0
AND locate( jpr.grid_name, jg.grid_name )> 0
diff --git a/src/main/java/org/springblade/modules/grid/mapper/GridWorkLogMapper.xml b/src/main/java/org/springblade/modules/grid/mapper/GridWorkLogMapper.xml
index b1e9eee..bf70153 100644
--- a/src/main/java/org/springblade/modules/grid/mapper/GridWorkLogMapper.xml
+++ b/src/main/java/org/springblade/modules/grid/mapper/GridWorkLogMapper.xml
@@ -14,9 +14,9 @@
bu.real_name as createUserName
from jczz_grid_work_log jgwl
left join jczz_household jh on jgwl.household_id = jh.id and jh.is_deleted = 0
+ left join jczz_house jhs on jhs.house_code = jh.house_code and jhs.is_deleted = 0
LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jh.house_code
- LEFT JOIN jczz_grid_range jgr on jgr.house_code=jda.address_code
- LEFT JOIN jczz_grid jg on jg.id = jgr.grid_id and jg.is_deleted = 0
+ LEFT JOIN jczz_grid jg on jhs.grid_code = jg.grid_code and jg.is_deleted = 0
LEFT JOIN blade_user bu on bu.id = jgwl.create_user and bu.is_deleted = 0
where jgwl.is_deleted = 0
<if test="gridWorkLog.type !=null">
@@ -30,9 +30,6 @@
</if>
<if test="gridWorkLog.personType !=null">
and jgwl.person_type = #{gridWorkLog.personType}
- </if>
- <if test="gridWorkLog.gridId !=null">
- and jgr.grid_id = #{gridWorkLog.gridId}
</if>
<if test="gridWorkLog.name !=null and gridWorkLog.name!=''">
and jh.name like concat('%',#{gridWorkLog.name},'%')
@@ -48,6 +45,9 @@
</if>
<if test="gridWorkLog.neiName!=null and gridWorkLog.neiName!=''">
and jda.nei_name like concat('%',#{gridWorkLog.neiName},'%')
+ </if>
+ <if test="gridWorkLog.gridId !=null">
+ and jg.id = #{gridWorkLog.gridId}
</if>
<if test="isAdministrator==2">
<choose>
@@ -68,7 +68,7 @@
<select id="getGridWorkCountHandleCount" resultType="java.lang.Integer">
select count(*) from jczz_grid_work_log jgwl
left join jczz_household jh on jgwl.household_id = jh.id and jh.is_deleted = 0
- left join jczz_house jhs on jhs.house_code=jh.house_code
+ left join jczz_house jhs on jhs.house_code=jh.house_code and jhs.is_deleted = 0
where jgwl.is_deleted = 0
<if test="status!=null">
and jgwl.status = #{status}
diff --git a/src/main/java/org/springblade/modules/grid/mapper/GridmanMapper.xml b/src/main/java/org/springblade/modules/grid/mapper/GridmanMapper.xml
index 0f3c3bb..fc69a04 100644
--- a/src/main/java/org/springblade/modules/grid/mapper/GridmanMapper.xml
+++ b/src/main/java/org/springblade/modules/grid/mapper/GridmanMapper.xml
@@ -25,7 +25,7 @@
br.name as communityName,
br.town_name as townName
from jczz_gridman jgm
- left join jczz_grid jg on jg.id = jgm.grid_id and jg.is_deleted = 0
+ left join jczz_grid jg on jg.grid_code = jgm.grid_code and jg.is_deleted = 0
left join blade_region br on br.code = jg.community_code
where jgm.is_deleted = 0
<if test="gridman.gridmanName!=null and gridman.gridmanName!=''">
@@ -78,7 +78,7 @@
<select id="getGridStatistics" resultType="java.lang.Integer">
SELECT count(1) number
FROM jczz_gridman jgm
- LEFT JOIN jczz_grid jg ON jg.id = jgm.grid_id
+ LEFT JOIN jczz_grid jg ON jg.grid_code = jgm.grid_code
WHERE jg.community_code = #{code}
AND jg.is_deleted = 0
<if test="userId!=null and roleType == '1'">
@@ -101,9 +101,8 @@
jda.aoi_code
FROM
jczz_grid jg
- LEFT JOIN jczz_gridman jgm ON jg.id = jgm.grid_id
- LEFT JOIN jczz_grid_range jgr ON jgr.grid_id = jg.id
- LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jgr.house_code
+ LEFT JOIN jczz_gridman jgm ON jg.grid_code = jgm.grid_code
+ LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jg.house_code
WHERE
jgm.user_id = #{userId}
AND jg.is_deleted = 0
@@ -126,9 +125,8 @@
jda.aoi_code
FROM
jczz_grid jg
- LEFT JOIN jczz_gridman jgm ON jg.id = jgm.grid_id
- LEFT JOIN jczz_grid_range jgr ON jgr.grid_id = jg.id
- LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jgr.house_code
+ LEFT JOIN jczz_gridman jgm ON jg.grid_code = jgm.grid_code
+ LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jg.house_code
WHERE
jgm.user_id = #{userId}
AND jg.is_deleted = 0
@@ -143,7 +141,7 @@
select
jgm.*,jg.community_code communityCode
from jczz_gridman jgm
- left join jczz_grid jg on jg.id = jgm.grid_id and jg.is_deleted = 0
+ left join jczz_grid jg on jg.grid_code = jgm.grid_code and jg.is_deleted = 0
where jgm.is_deleted = 0
and jgm.id = #{gridman.id}
</select>
@@ -159,14 +157,18 @@
</select>
<select id="getGridManByCode" resultType="org.springblade.modules.system.entity.User">
-SELECT
- *
-FROM
- blade_user bu
- LEFT JOIN jczz_gridman jgm ON bu.id = jgm.user_id
- LEFT JOIN jczz_grid jg ON jgm.grid_id = jg.id
-WHERE
- jg.community_code IN ( SELECT jg.community_code FROM jczz_grid jg LEFT JOIN jczz_grid_range jgr ON jgr.grid_id = jg.id WHERE jgr.house_code = #{houseCode} )
- AND bu.is_deleted = 0
+ SELECT
+ bu.*
+ FROM
+ blade_user bu
+ LEFT JOIN jczz_gridman jgm ON bu.id = jgm.user_id
+ LEFT JOIN jczz_grid jg ON jgm.grid_code = jg.grid_code
+ WHERE
+ jg.community_code IN
+ (
+ SELECT jg.community_code FROM jczz_grid jg LEFT JOIN jczz_grid_range jgr ON jgr.grid_id = jg.id
+ WHERE jgr.house_code = #{houseCode}
+ )
+ AND bu.is_deleted = 0
</select>
</mapper>
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml b/src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml
index 3f9e50e..ac51552 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseMapper.xml
@@ -244,11 +244,10 @@
select
jh.*,
jhl.id as cid,jhl.*,jhl.remark as cremark,
- br.code as neiCode,jg.id as gridId,br.town_code as streetCode
+ br.code as neiCode,br.town_code as streetCode
from jczz_house jh
left join jczz_user_house_label jhl on jh.house_code = jhl.house_code and jhl.lable_type = 2
- left join jczz_grid_range jgr on jgr.house_code = jh.house_code
- left join jczz_grid jg on jg.id = jgr.grid_id and jg.is_deleted = 0
+ left join jczz_grid jg on jg.grid_code = jgr.grid_code and jg.is_deleted = 0
left join blade_region br on br.code = jg.community_code
where jh.is_deleted = 0
<if test="house.houseCode!=null and house.houseCode!=''">
diff --git a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
index 4e624b9..efbea42 100644
--- a/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
+++ b/src/main/java/org/springblade/modules/house/mapper/HouseRentalMapper.xml
@@ -64,9 +64,8 @@
if(jhr.termination_time is null,if(date_format(jhr.due_time,'%Y-%m-%d') >= date_format(now(),'%Y-%m-%d'),0,1),2) as status
FROM jczz_house_rental jhr
JOIN jczz_doorplate_address jda ON jda.address_code = jhr.house_code
- JOIN jczz_house jh ON jh.house_code = jhr.house_code
- LEFT JOIN jczz_grid_range jgr on jgr.house_code=jda.address_code
- LEFT JOIN jczz_grid jg on jg.id = jgr.grid_id and jg.is_deleted = 0
+ JOIN jczz_house jh ON jh.house_code = jhr.house_code and jh.is_deleted = 0
+ LEFT JOIN jczz_grid jg on jg.grid_code = jh.grid_code and jg.is_deleted = 0
LEFT JOIN (
SELECT jht.housing_rental_id,jht.name as tenantName,jht.phone_number as phone
FROM jczz_household jht RIGHT JOIN (
diff --git a/src/main/java/org/springblade/modules/house/service/impl/HouseServiceImpl.java b/src/main/java/org/springblade/modules/house/service/impl/HouseServiceImpl.java
index 82cd027..ac81170 100644
--- a/src/main/java/org/springblade/modules/house/service/impl/HouseServiceImpl.java
+++ b/src/main/java/org/springblade/modules/house/service/impl/HouseServiceImpl.java
@@ -89,14 +89,6 @@
List<String> regionChildCodesList = SysCache.getRegionChildCodesByDeptId(AuthUtil.getDeptId());
Integer isAdministrator = AuthUtil.isAdministrator()==true?1:2;
List<HouseVO> houseVOS = baseMapper.selectHousePage(page, house,regionChildCodesList,isAdministrator);
- // 遍历查询网格
-// for (HouseVO houseVO : houseVOS) {
-// // 设置对应的网格名称
-// GridVO gridVO = gridService.getGridDetailByHouseCode(houseVO.getHouseCode());
-// if (null!= gridVO){
-// houseVO.setGridName(gridVO.getGridName());
-// }
-// }
return page.setRecords(houseVOS);
}
diff --git a/src/main/java/org/springblade/modules/place/mapper/PlaceExtMapper.xml b/src/main/java/org/springblade/modules/place/mapper/PlaceExtMapper.xml
index 124ed96..d6a1888 100644
--- a/src/main/java/org/springblade/modules/place/mapper/PlaceExtMapper.xml
+++ b/src/main/java/org/springblade/modules/place/mapper/PlaceExtMapper.xml
@@ -17,10 +17,8 @@
jp.place_name AS placeName
FROM
jczz_place_ext jpe
- LEFT JOIN jczz_place jp ON jpe.place_id = jp.id
- AND jp.is_deleted = 0
- LEFT JOIN jczz_grid_range jgr on jgr.house_code=jp.house_code
- LEFT JOIN jczz_grid jg on jg.id = jgr.grid_id
+ LEFT JOIN jczz_place jp ON jpe.place_id = jp.id AND jp.is_deleted = 0
+ LEFT JOIN jczz_grid jg ON jp.grid_code = jg.grid_code AND jg.is_deleted = 0
WHERE
jpe.is_deleted = 0
<if test="isAdministrator==2">
diff --git a/src/main/java/org/springblade/modules/place/mapper/PlaceMapper.java b/src/main/java/org/springblade/modules/place/mapper/PlaceMapper.java
index c896fc0..2c43131 100644
--- a/src/main/java/org/springblade/modules/place/mapper/PlaceMapper.java
+++ b/src/main/java/org/springblade/modules/place/mapper/PlaceMapper.java
@@ -39,10 +39,13 @@
* @param page
* @param place
* @param houseCodeList
+ * @param regionChildCodesList
+ * @param isAdministrator
* @return
*/
List<PlaceVO> selectPlacePage(IPage page,
@Param("place") PlaceVO place,
+ @Param("houseCodeList") List<String> houseCodeList,
@Param("regionChildCodesList") List<String> regionChildCodesList,
@Param("isAdministrator") Integer isAdministrator);
diff --git a/src/main/java/org/springblade/modules/place/mapper/PlaceMapper.xml b/src/main/java/org/springblade/modules/place/mapper/PlaceMapper.xml
index 3d1062e..a2cbadc 100644
--- a/src/main/java/org/springblade/modules/place/mapper/PlaceMapper.xml
+++ b/src/main/java/org/springblade/modules/place/mapper/PlaceMapper.xml
@@ -63,16 +63,22 @@
<if test="place.isPerfect==2">
and jp.status = 2
</if>
+ <if test="houseCodeList != null and houseCodeList.size()>0">
+ and jp.house_code in
+ <foreach collection="houseCodeList" item="houseCode" separator ="," open="(" close=")">
+ #{houseCode}
+ </foreach>
+ </if>
<if test="isAdministrator==2">
<choose>
<when test="regionChildCodesList !=null and regionChildCodesList.size()>0">
- and jg.community_code in
+ and jg.grid_code in
<foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=",">
#{code}
</foreach>
</when>
<otherwise>
- and jg.community_code in ('')
+ and jg.grid_code in ('')
</otherwise>
</choose>
</if>
@@ -87,8 +93,7 @@
br.name neiName,
FALSE AS hasChildren
FROM jczz_place jp
- LEFT JOIN jczz_grid_range jgr ON jp.house_code = jgr.house_code
- LEFT JOIN jczz_grid jg on jg.id = jgr.grid_id
+ LEFT JOIN jczz_grid jg on jp.grid_code = jg.grid_code and jg.is_deleted = 0
LEFT JOIN blade_region br on br.code = jg.community_code
where 1 = 1
and jp.is_deleted = 0
diff --git a/src/main/java/org/springblade/modules/place/service/impl/PlaceServiceImpl.java b/src/main/java/org/springblade/modules/place/service/impl/PlaceServiceImpl.java
index 29be09c..481f502 100644
--- a/src/main/java/org/springblade/modules/place/service/impl/PlaceServiceImpl.java
+++ b/src/main/java/org/springblade/modules/place/service/impl/PlaceServiceImpl.java
@@ -107,7 +107,14 @@
public IPage<PlaceVO> selectPlacePage(IPage<PlaceVO> page, PlaceVO place) {
List<String> regionChildCodesList = SysCache.getRegionChildCodesByDeptId(AuthUtil.getDeptId());
Integer isAdministrator = AuthUtil.isAdministrator()==true?1:2;
- List<PlaceVO> placeVOS = baseMapper.selectPlacePage(page, place,regionChildCodesList,isAdministrator);
+ List<String> list = new ArrayList<>();
+ if (null!=place.getRoleName() && !place.getRoleName().equals("")){
+ if (place.getRoleName().equals("网格员")){
+ // 查询对应的房屋地址code
+ list = gridService.getAddressCodeListByUserId(AuthUtil.getUserId());
+ }
+ }
+ List<PlaceVO> placeVOS = baseMapper.selectPlacePage(page, place,list,regionChildCodesList,isAdministrator);
// 返回
return page.setRecords(placeVOS);
}
diff --git a/src/main/java/org/springblade/modules/system/mapper/RegionMapper.java b/src/main/java/org/springblade/modules/system/mapper/RegionMapper.java
index 2eb46c2..f238f50 100644
--- a/src/main/java/org/springblade/modules/system/mapper/RegionMapper.java
+++ b/src/main/java/org/springblade/modules/system/mapper/RegionMapper.java
@@ -91,4 +91,11 @@
* @return
*/
List<Region> getCodeLess12DataList();
+
+ /**
+ * 查询下级区域(拼接网格)
+ * @param regionCode
+ * @return
+ */
+ List<Region> getRegionChildList(@Param("regionCode") String regionCode);
}
diff --git a/src/main/java/org/springblade/modules/system/mapper/RegionMapper.xml b/src/main/java/org/springblade/modules/system/mapper/RegionMapper.xml
index 60f6972..caee645 100644
--- a/src/main/java/org/springblade/modules/system/mapper/RegionMapper.xml
+++ b/src/main/java/org/springblade/modules/system/mapper/RegionMapper.xml
@@ -188,4 +188,25 @@
where length(parent_code) < 12
</select>
+ <!--查询下级区域(拼接网格)-->
+ <select id="getRegionChildList" resultType="org.springblade.modules.system.entity.Region">
+ SELECT
+ code,parent_code,name,ancestors,region_level,sort
+ FROM blade_region
+ where ancestors like concat('%',#{regionCode},'%')
+ union all
+ (
+ SELECT
+ jg.grid_code as code,
+ jg.community_code as parent_code,
+ jg.grid_name as name,
+ '' as ancestors,
+ 6 as region_level,
+ jg.sort
+ FROM blade_region br
+ right join jczz_grid jg on jg.community_code = br.village_code and jg.is_deleted = 0
+ where br.ancestors like concat('%',#{regionCode},'%')
+ )
+ </select>
+
</mapper>
diff --git a/src/main/java/org/springblade/modules/system/service/impl/RegionServiceImpl.java b/src/main/java/org/springblade/modules/system/service/impl/RegionServiceImpl.java
index 470736d..da560ec 100644
--- a/src/main/java/org/springblade/modules/system/service/impl/RegionServiceImpl.java
+++ b/src/main/java/org/springblade/modules/system/service/impl/RegionServiceImpl.java
@@ -215,12 +215,12 @@
}
/**
- * 查询下级区域
+ * 查询下级区域(拼接网格)
* @param regionCode
* @return
*/
@Override
public List<Region> getRegionChild(String regionCode) {
- return baseMapper.selectList(Wrappers.<Region>query().lambda().like(Region::getAncestors, regionCode));
+ return baseMapper.getRegionChildList(regionCode);
}
}
--
Gitblit v1.9.3