From 6ad9c778d138d0806966ce044dd4ee2ee46d2ea2 Mon Sep 17 00:00:00 2001
From: Administrator <admin>
Date: Sat, 18 Sep 2021 16:56:52 +0800
Subject: [PATCH] 分局预警接口修改
---
src/main/java/org/springblade/modules/system/mapper/UserMapper.xml | 8
src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml | 551 +++++++++++++++++++++++++++++++++++++++++-------------
src/main/java/org/springblade/modules/information/controller/InformationsController.java | 39 ++-
3 files changed, 448 insertions(+), 150 deletions(-)
diff --git a/src/main/java/org/springblade/modules/information/controller/InformationsController.java b/src/main/java/org/springblade/modules/information/controller/InformationsController.java
index 922df69..d21b8a6 100644
--- a/src/main/java/org/springblade/modules/information/controller/InformationsController.java
+++ b/src/main/java/org/springblade/modules/information/controller/InformationsController.java
@@ -149,6 +149,32 @@
//辖区信息
List<Map<Object, Object>> list = informationService.selectJur();
List<Map<String, Object>> lists = new ArrayList<>();
+
+ //1.保安员表现差预警数量
+ List<ResponseVo> performanceGroupCountList = informationService.getSecurityPerformanceGroupCountList();
+ System.out.println("保安员表现差预警数量 = " + performanceGroupCountList);
+
+ //2.保安员资格异常的数量
+ List<ResponseVo> examinationGroupCountList = informationService.getSecurityExaminationGroupCountList();
+ System.out.println("保安员资格异常的数量 = " + examinationGroupCountList);
+
+ //3.考试通过率低于50%
+ List<ResponseVo> examPassingGroupCountList = informationService.getSecurityExamPassingGroupCountList();
+ System.out.println("考试通过率低于50% = " + examPassingGroupCountList);
+
+ //4.持证率低于50%
+ List<ResponseVo> holdGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(1);
+ System.out.println("持证率低于50% = " + holdGroupCountList);
+
+ //5.社保缴纳率低于50%
+ List<ResponseVo> soidGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(2);
+ System.out.println("社保缴纳率低于50% = " + soidGroupCountList);
+
+ //6.派遣率低于50%
+ List<ResponseVo> dispatchGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(3);
+ System.out.println("派遣率低于50% = " + dispatchGroupCountList);
+
+
for (int i = 0; i < list.size(); i++) {
int count = 0;
Map<String, Object> map = new HashMap<String, Object>();
@@ -156,9 +182,8 @@
String id = list.get(i).get("id").toString();
//辖区名称
String jurname = list.get(i).get("dept_name").toString();
+
//1.保安员表现差预警数量
- List<ResponseVo> performanceGroupCountList = informationService.getSecurityPerformanceGroupCountList();
- System.out.println("保安员表现差预警数量 = " + performanceGroupCountList);
//遍历计算总数
for (ResponseVo responseVo : performanceGroupCountList) {
//辖区id相同则数量相加
@@ -169,8 +194,6 @@
//2.保安员资格异常的数量
- List<ResponseVo> examinationGroupCountList = informationService.getSecurityExaminationGroupCountList();
- System.out.println("保安员资格异常的数量 = " + examinationGroupCountList);
//遍历计算总数
for (ResponseVo responseVo : examinationGroupCountList) {
//辖区id相同则数量相加
@@ -180,8 +203,6 @@
}
//3.考试通过率低于50%
- List<ResponseVo> examPassingGroupCountList = informationService.getSecurityExamPassingGroupCountList();
- System.out.println("考试通过率低于50% = " + examPassingGroupCountList);
//遍历计算总数
for (ResponseVo responseVo : examPassingGroupCountList) {
//辖区id相同则数量相加
@@ -191,8 +212,6 @@
}
//4.持证率低于50%
- List<ResponseVo> holdGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(1);
- System.out.println("持证率低于50% = " + holdGroupCountList);
//遍历计算总数
for (ResponseVo responseVo : holdGroupCountList) {
//辖区id相同则数量相加
@@ -202,8 +221,6 @@
}
//5.社保缴纳率低于50%
- List<ResponseVo> soidGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(2);
- System.out.println("社保缴纳率低于50% = " + soidGroupCountList);
//遍历计算总数
for (ResponseVo responseVo : soidGroupCountList) {
//辖区id相同则数量相加
@@ -213,8 +230,6 @@
}
//6.派遣率低于50%
- List<ResponseVo> dispatchGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(3);
- System.out.println("派遣率低于50% = " + dispatchGroupCountList);
//遍历计算总数
for (ResponseVo responseVo : dispatchGroupCountList) {
//辖区id相同则数量相加
diff --git a/src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml b/src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml
index c0bb6c1..a6d1996 100644
--- a/src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml
+++ b/src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml
@@ -2017,23 +2017,36 @@
<!--根据辖区查询统计保安公司(1:持证率低于50% 2:派遣率低于50% 3：缴纳社保率低于50% 的保安公司个数及保安公司分页信息（本市保安公司，分公司）-->
<select id="getSecurityUnitLess50Page" resultType="org.springblade.modules.information.vo.InformationVO">
- select c.*,sj.dept_name jurisdictionName from (
+ select c.*,sj.dept_name jurisdictionName,si.enterpriseName enterprisename from (
select
- si.*,
+ a.dept_id,
ifnull(a.count,0) znum,
ifnull(b.count,0) cnum,
ifnull(FORMAT((b.count/a.count)*100,2),0) pnum
- from sys_information si
- left join
+ from
(
- <include refid="getSecurity"/>
+ select dept_id,
+ ifnull(count(*), 0) count
+ from blade_user bu
+ left join blade_role br
+ on bu.role_id = br.id
+ where
+ role_alias = "安保人员"
+ and bu.is_deleted = 0
+ and bu.`status` = 1
group by dept_id
) a
- on
- a.dept_id = si.departmentid
left join
(
- <include refid="getSecurity"/>
+ select dept_id,
+ ifnull(count(*), 0) count
+ from blade_user bu
+ left join blade_role br
+ on bu.role_id = br.id
+ where
+ role_alias = "安保人员"
+ and bu.is_deleted = 0
+ and bu.`status` = 1
<if test="type==1">
and bu.hold = 1
</if>
@@ -2049,14 +2062,18 @@
group by dept_id
) b
on
- b.dept_id = si.departmentid
+ a.dept_id = b.dept_id
)c
+ left join
+ sys_information si
+ on
+ si.departmentid = c.dept_id
left join
sys_jurisdiction sj
on
- c.jurisdiction = sj.id
+ si.jurisdiction = sj.id
where 1=1
- and (c.stats = 2 or c.stats = 4)
+ and (si.stats = 2 or si.stats = 4)
and pnum < #{information.proportion}
<if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
and (sj.id = #{information.jurisdiction} or sj.parent_id = #{information.jurisdiction})
@@ -2179,82 +2196,344 @@
<!--根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，现实表现差，未缴纳社保，资格审查异常）-->
<select id="getSubstationWarnInfoPage" resultType="org.springblade.modules.information.vo.InformationVO">
- select jurisdictionName,enterprisename,detail from (
- (
- select
- si.jurisdiction id,
- sj.parent_id,
- sj.dept_name jurisdictionName,
- si.enterprisename,
- "表现差" detail
- from sys_performance sp
- left join
- blade_user bu
- on
- bu.id = sp.securityId
- left join
- sys_information si
- on
- bu.dept_id = si.departmentid
- left join
- sys_jurisdiction sj
- on
- sj.id = si.jurisdiction
- where
- 1=1
- and (si.stats = 2 or si.stats =4)
- and bu.is_deleted = 0
- and bu.`status` = 1
- and sp.score = 3
- )
-
- union
- (
- select
- sj.id,
- sj.parent_id,
- sj.dept_name jurisdictionName,
- si.enterprisename,
- if(bu.hold=2,"未持证","证件吊销") as detail
- from
- blade_user bu
- <include refid="getSubstationWarn"/>
- and (bu.hold = 2 or bu.hold = 3)
- )
-
-
- union
- (
- select
- sj.id,
- sj.parent_id,
- sj.dept_name jurisdictionName,
- si.enterprisename,
- "未缴纳社保" as detail
- from
- blade_user bu
- <include refid="getSubstationWarn"/>
- and bu.soil = 1
- )
-
- union
- (
- select
- sj.id,
- sj.parent_id,
- sj.dept_name jurisdictionName,
- si.enterprisename,
- "审查异常" as detail
- from
- blade_user bu
- <include refid="getSubstationWarn"/>
- and bu.examination_type = 1
- )
+ (select
+ sj1.dept_name jurisdictionName,
+ si1.enterpriseName enterprisename,
+ "表现差" detail,
+ a.count znum
+ from (
+ select
+ bu.dept_id,count(*) count
+ from sys_performance sp
+ left join
+ blade_user bu
+ on
+ bu.id = sp.securityId
+ where
+ 1=1
+ and bu.is_deleted = 0
+ and bu.`status` = 1
+ and sp.score = 3
+ GROUP BY bu.dept_id
) a
+ left join
+ sys_information si1
+ on
+ a.dept_id = si1.departmentid
+ left join
+ sys_jurisdiction sj1
+ on
+ sj1.id = si1.jurisdiction
where 1=1
+ and (si1.stats = 2 or si1.stats =4)
<if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
- and (a.id = #{information.jurisdiction} or a.parent_id = #{information.jurisdiction})
+ and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
</if>
+ )
+
+ union
+
+ (
+ select
+ sj1.dept_name jurisdictionName,
+ si1.enterpriseName enterprisename,
+ "资格审查异常" detail,
+ a.count znum
+ from (
+ select
+ bu.dept_id,count(*) count
+ from
+ blade_user bu
+ where
+ 1=1
+ and bu.is_deleted = 0
+ and bu.`status` = 1
+ and bu.examination_type = 1
+ GROUP BY bu.dept_id
+ ) a
+ left join
+ sys_information si1
+ on
+ a.dept_id = si1.departmentid
+ left join
+ sys_jurisdiction sj1
+ on
+ sj1.id = si1.jurisdiction
+ where 1=1
+ and (si1.stats = 2 or si1.stats =4)
+ <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
+ and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
+ </if>
+ )
+
+ union
+
+ (
+ select
+ sj1.dept_name jurisdictionName,
+ si1.enterpriseName enterprisename,
+ "考试通过率低于50%" detail,
+ e.count znum
+ from
+ (
+ select d.dept_id,count(*) count from (
+ select a.dept_id, ifnull(b.count, 0) num1, ifnull(c.count, 0) num2,ifnull(FORMAT((c.count/b.count)*100, 2), 0) num3 from
+ (
+ select
+ bu.dept_id, ifnull(count(*), 0) count
+ from blade_user bu
+ left join blade_role br
+ on bu.role_id = br.id
+ where
+ role_alias = "安保人员"
+ and bu.is_deleted = 0
+ and bu.`status` = 1
+ group by dept_id
+ ) a
+ left join
+ (
+ select count(*) count, bu.dept_id from
+ exam_score es
+ left join
+ blade_user bu
+ on
+ bu.id = es.user_id
+ where 1=1 group by bu.dept_id
+ ) b
+ on
+ a.dept_id = b.dept_id
+ left join
+ (
+ select count(*) count, bu.dept_id from
+ exam_score es
+ left join
+ blade_user bu
+ on
+ bu.id = es.user_id
+ where qualified = 0 group by bu.dept_id
+ ) c
+ on
+ c.dept_id = a.dept_id
+ where 1=1
+ ) d
+ where 1=1
+ and num1!=0
+ and num3 < 50
+ GROUP BY dept_id
+ ) e
+ left join
+ sys_information si1
+ on
+ e.dept_id = si1.departmentid
+ left join
+ sys_jurisdiction sj1
+ on
+ sj1.id = si1.jurisdiction
+ where 1=1
+ and (stats = 2 or stats=4)
+ <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
+ and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
+ </if>
+ )
+
+ union
+
+ (
+ select
+ sj1.dept_name jurisdictionName,
+ si1.enterpriseName enterprisename,
+ "持证率低于50%" detail,
+ e.count znum
+ from
+ (
+ select c.dept_id,count(*) count from (
+ SELECT
+ a.dept_id,
+ ifnull(a.count,0) num1,
+ ifnull(b.count,0) num2,
+ ifnull(FORMAT((b.count/a.count)*100,2),0) num3
+ FROM (
+ SELECT
+ bu.dept_id,
+ ifnull(count(*),0) count
+ FROM
+ blade_user bu
+ LEFT JOIN blade_role br ON bu.role_id = br.id
+ WHERE
+ role_alias = "安保人员"
+ AND bu.is_deleted = 0
+ AND bu.`status` = 1
+ GROUP BY
+ dept_id
+ ) a
+
+ LEFT JOIN (
+ SELECT
+ bu.dept_id,
+ ifnull(count(*),0) count
+ FROM
+ blade_user bu
+ LEFT JOIN blade_role br ON bu.role_id = br.id
+ WHERE
+ role_alias = "安保人员"
+ AND bu.is_deleted = 0
+ AND bu.`status` = 1
+ AND bu.hold = 1
+ GROUP BY
+ dept_id
+ ) b ON a.dept_id = b.dept_id
+ ) c
+ where 1=1
+ and num3 < 50
+ GROUP BY c.dept_id
+ ) e
+ left join
+ sys_information si1
+ on
+ e.dept_id = si1.departmentid
+ left join
+ sys_jurisdiction sj1
+ on
+ sj1.id = si1.jurisdiction
+ where 1=1
+ AND ( stats = 2 OR stats = 4 )
+ <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
+ and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
+ </if>
+ )
+
+ union
+
+ (
+ select
+ sj1.dept_name jurisdictionName,
+ si1.enterpriseName enterprisename,
+ "社保缴纳率低于50%" detail,
+ e.count znum
+ from
+ (
+ select c.dept_id,count(*) count from (
+ SELECT
+ a.dept_id,
+ ifnull(a.count,0) num1,
+ ifnull(b.count,0) num2,
+ ifnull(FORMAT((b.count/a.count)*100,2),0) num3
+ FROM (
+ SELECT
+ bu.dept_id,
+ ifnull(count(*),0) count
+ FROM
+ blade_user bu
+ LEFT JOIN blade_role br ON bu.role_id = br.id
+ WHERE
+ role_alias = "安保人员"
+ AND bu.is_deleted = 0
+ AND bu.`status` = 1
+ GROUP BY
+ dept_id
+ ) a
+
+ LEFT JOIN (
+ SELECT
+ bu.dept_id,
+ ifnull(count(*),0) count
+ FROM
+ blade_user bu
+ LEFT JOIN blade_role br ON bu.role_id = br.id
+ WHERE
+ role_alias = "安保人员"
+ AND bu.is_deleted = 0
+ AND bu.`status` = 1
+ AND bu.soil = 0
+ GROUP BY
+ dept_id
+ ) b ON a.dept_id = b.dept_id
+ ) c
+ where 1=1
+ and num3 < 50
+ GROUP BY c.dept_id
+ ) e
+ left join
+ sys_information si1
+ on
+ e.dept_id = si1.departmentid
+ left join
+ sys_jurisdiction sj1
+ on
+ sj1.id = si1.jurisdiction
+ where 1=1
+ AND ( stats = 2 OR stats = 4 )
+ <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
+ and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
+ </if>
+ )
+
+ union
+
+ (
+ select
+ sj1.dept_name jurisdictionName,
+ si1.enterpriseName enterprisename,
+ "派遣率低于50%" detail,
+ e.count znum
+ from
+ (
+ select c.dept_id,count(*) count from (
+ SELECT
+ a.dept_id,
+ ifnull(a.count,0) num1,
+ ifnull(b.count,0) num2,
+ ifnull(FORMAT((b.count/a.count)*100,2),0) num3
+ FROM (
+ SELECT
+ bu.dept_id,
+ ifnull(count(*),0) count
+ FROM
+ blade_user bu
+ LEFT JOIN blade_role br ON bu.role_id = br.id
+ WHERE
+ role_alias = "安保人员"
+ AND bu.is_deleted = 0
+ AND bu.`status` = 1
+ GROUP BY
+ dept_id
+ ) a
+
+ LEFT JOIN (
+ SELECT
+ bu.dept_id,
+ ifnull(count(*),0) count
+ FROM
+ blade_user bu
+ LEFT JOIN blade_role br ON bu.role_id = br.id
+ WHERE
+ role_alias = "安保人员"
+ AND bu.is_deleted = 0
+ AND bu.`status` = 1
+ AND bu.dispatch = 0
+ GROUP BY
+ dept_id
+ ) b ON a.dept_id = b.dept_id
+ ) c
+ where 1=1
+ and num3 < 50
+ GROUP BY c.dept_id
+ ) e
+ left join
+ sys_information si1
+ on
+ e.dept_id = si1.departmentid
+ left join
+ sys_jurisdiction sj1
+ on
+ sj1.id = si1.jurisdiction
+ where 1=1
+ AND ( stats = 2 OR stats = 4 )
+ <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
+ and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
+ </if>
+ )
+
</select>
<!--根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司））服务对象小于2家的公司数量的分页信息-->
@@ -2305,7 +2584,7 @@
<!--统计表现差的信息-->
<select id="getSecurityPerformanceGroupCountList" resultType="org.springblade.modules.information.vo.ResponseVo">
select a.id,
- a.dept_name name,
+ a.dept_name name,
ifnull(b.count, 0) num
from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) a
left join
@@ -2336,7 +2615,7 @@
group by si.jurisdiction
) b
on
- a.id = b.parent_id
+ a.id = b.parent_id
</select>
@@ -2510,57 +2789,57 @@
(select id,dept_name from sys_jurisdiction where parent_id = 1372091709474910209) g
left join
(
- select c.id,c.parent_id,count(*) count from (
- select si.enterpriseName,sj.id,sj.parent_id,a.dept_id,ifnull(a.count,0) num1,ifnull(b.count,0) num2,ifnull(FORMAT((b.count/a.count)*100,2),0) num3 from sys_information si
- left join
- (
- select
- bu.dept_id,ifnull(count(*),0) count
- from blade_user bu
- left join blade_role br
- on bu.role_id = br.id
- where
- role_alias = "安保人员"
- and bu.is_deleted = 0
- and bu.`status` = 1
- group by dept_id
- ) a
- on
- si.departmentid = a.dept_id
-
- left join
- (
- select
- bu.dept_id,ifnull(count(*),0) count
- from blade_user bu
- left join blade_role br
- on bu.role_id = br.id
- where
- role_alias = "安保人员"
- and bu.is_deleted = 0
- and bu.`status` = 1
- <if test="type==1">
- and bu.hold = 1
- </if>
- <if test="type==2">
- and bu.dispatch = 0
- </if>
- <if test="type==3">
- and bu.soil = 0
- </if>
- group by dept_id
- ) b
- on
- si.departmentid = b.dept_id
- left join
- sys_jurisdiction sj
- on
- sj.id = si.jurisdiction
- where 1=1
- and (stats = 2 or stats=4)
- )c
- where c.num3 < 50
- group by c.id
+ select c.id,c.parent_id,count(*) count from (
+ select si.enterpriseName,sj.id,sj.parent_id,a.dept_id,ifnull(a.count,0) num1,ifnull(b.count,0) num2,ifnull(FORMAT((b.count/a.count)*100,2),0) num3 from
+ (
+ select
+ bu.dept_id,ifnull(count(*),0) count
+ from blade_user bu
+ left join blade_role br
+ on bu.role_id = br.id
+ where
+ role_alias = "安保人员"
+ and bu.is_deleted = 0
+ and bu.`status` = 1
+ group by dept_id
+ ) a
+ left join
+ (
+ select
+ bu.dept_id,ifnull(count(*),0) count
+ from blade_user bu
+ left join blade_role br
+ on bu.role_id = br.id
+ where
+ role_alias = "安保人员"
+ and bu.is_deleted = 0
+ and bu.`status` = 1
+ <if test="type==1">
+ and bu.hold = 1
+ </if>
+ <if test="type==2">
+ and bu.dispatch = 0
+ </if>
+ <if test="type==3">
+ and bu.soil = 0
+ </if>
+ group by dept_id
+ ) b
+ on
+ a.dept_id = b.dept_id
+ left join
+ sys_information si
+ on
+ a.dept_id = si.departmentid
+ left join
+ sys_jurisdiction sj
+ on
+ sj.id = si.jurisdiction
+ where 1=1
+ and (stats = 2 or stats=4)
+ )c
+ where c.num3 < 50
+ group by c.id
) f
on
g.id = f.parent_id
diff --git a/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml b/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
index f22d648..84135c1 100644
--- a/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
+++ b/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
@@ -157,7 +157,11 @@
sys_information si
on
si.departmentid = bu.dept_id
- where is_deleted = 0
+ left join
+ sys_jurisdiction sj
+ on
+ sj.id =si.jurisdiction
+ where bu.is_deleted = 0
and status = 1
<if test="user.realName!=null and user.realName != ''">
and bu.real_name like concat(concat('%', #{user.realName}),'%')
@@ -181,7 +185,7 @@
and bu.hold = #{user.hold}
</if>
<if test="user.jurisdiction!=null and user.jurisdiction != '' and user.jurisdiction != '1372091709474910209'">
- and si.jurisdiction = #{user.jurisdiction}
+ and sj.parent_id = #{user.jurisdiction}
</if>
<if test="user.securitynumber!=null and user.securitynumber != ''">
and bu.securitynumber = #{user.securitynumber}
--
Gitblit v1.9.3