From 96d31712c6654b2b71ed04696a9bb30af4d7f79a Mon Sep 17 00:00:00 2001
From: tangzy <tangzy123456>
Date: Thu, 23 Sep 2021 09:39:49 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master'
---
src/main/java/org/springblade/modules/dispatcher/vo/DispatcherUnitVO.java | 5
src/main/java/org/springblade/modules/information/vo/ResponseVo.java | 21 +
src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherUnitMapper.xml | 1
src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml | 7
src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml | 711 +++++++++++++++++++++++++++++++++++++++----
src/main/java/org/springblade/modules/information/service/IInformationService.java | 26 +
src/main/java/org/springblade/modules/information/controller/InformationsController.java | 107 ++++++
src/main/java/org/springblade/modules/information/vo/InformationVO.java | 2
src/main/java/org/springblade/modules/information/mapper/InformationMapper.java | 27 +
src/main/java/org/springblade/modules/information/service/impl/InformationServiceImpl.java | 36 ++
10 files changed, 866 insertions(+), 77 deletions(-)
diff --git a/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherUnitMapper.xml b/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherUnitMapper.xml
index 0bd7c8e..6a35000 100644
--- a/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherUnitMapper.xml
+++ b/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherUnitMapper.xml
@@ -5,6 +5,7 @@
<!--派遣公司分页信息-->
<select id="selectDispatcherUnitPage" resultType="org.springblade.modules.dispatcher.vo.DispatcherUnitVO">
select
+ distinct
sdu.*,bt.dept_name tenantName,bu.real_name realName
from
sys_dispatcher_unit sdu
diff --git a/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherUnitVO.java b/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherUnitVO.java
index cc762c9..5201e3b 100644
--- a/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherUnitVO.java
+++ b/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherUnitVO.java
@@ -46,4 +46,9 @@
* 合同是否到期 1：否 2：是
*/
private Integer isExpire;
+
+ /**
+ * 保安姓名
+ */
+ private String realName;
}
diff --git a/src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml b/src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml
index 2348bd0..e30a281 100644
--- a/src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml
+++ b/src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml
@@ -6,7 +6,7 @@
<select id="selectExamScorePage" resultType="org.springblade.modules.exam.vo.ExamScoreVO">
SELECT
es.id,es.theory_grade theoryGrade,ifnull(learn_grade,-1) learnGrade,
- exam_name examName,bu.real_name securityName,company,exam_time examTime,
+ ke.exam_name examName,bu.real_name securityName,company,es.exam_time examTime,
all_grade allGrade,exam_end_time examEndTime,qualified,
bd.dept_name companyName
FROM
@@ -19,6 +19,10 @@
blade_dept bd
on
bd.id = es.company
+ left join
+ ksxt_exam ke
+ on
+ ke.id = es.exam_id
WHERE
1=1
<if test="examScore.examName!=null and examScore.examName!=''">
@@ -39,6 +43,7 @@
<if test="examScore.qualified!=null">
and es.qualified = #{examScore.qualified}
</if>
+ order by es.id desc
</select>
<!--考试成绩详情信息-->
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 a72081d..35304ef 100644
--- a/src/main/java/org/springblade/modules/information/controller/InformationsController.java
+++ b/src/main/java/org/springblade/modules/information/controller/InformationsController.java
@@ -7,9 +7,15 @@
import org.springblade.core.tool.api.R;
import org.springblade.modules.information.service.IInformationService;
import org.springblade.modules.information.vo.InformationVO;
+import org.springblade.modules.information.vo.ResponseVo;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
/**
* 组织机构详情控制层 2
@@ -133,4 +139,105 @@
public R<IPage<InformationVO>> getSecurityUnitServerLessPage(InformationVO information, Query query){
return R.data(informationService.getSecurityUnitServerLess2Page(Condition.getPage(query), information));
}
+
+ /**
+ * 首页分局预警统计(保安员预警（表现差，审查异常），保安公司预警（考试通过率低于50%，持证率低于50%，社保缴纳率低于50%，派遣率低于50%）)
+ * @return
+ */
+ @GetMapping("/selectSubstationWarnInfo")
+ public R selectSubstationWarnInfo() {
+ //辖区信息
+ List<Map<Object, Object>> list = informationService.selectJur();
+ List<Map<String, Object>> lists = new ArrayList<>();
+
+ //1.保安员表现差预警数量
+ List<ResponseVo> performanceGroupCountList = informationService.getSecurityPerformanceGroupCountList();
+
+ //2.保安员资格异常的数量
+ List<ResponseVo> examinationGroupCountList = informationService.getSecurityExaminationGroupCountList();
+
+ //3.考试通过率低于50%
+ List<ResponseVo> examPassingGroupCountList = informationService.getSecurityExamPassingGroupCountList();
+
+ //4.持证率低于50%
+ List<ResponseVo> holdGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(1);
+
+ //5.社保缴纳率低于50%
+ List<ResponseVo> soidGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(2);
+
+ //6.派遣率低于50%
+ List<ResponseVo> dispatchGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(3);
+
+
+ for (int i = 0; i < list.size(); i++) {
+ int count = 0;
+ Map<String, Object> map = new HashMap<String, Object>();
+ //辖区id
+ String id = list.get(i).get("id").toString();
+ //辖区名称
+ String jurname = list.get(i).get("dept_name").toString();
+
+ //1.保安员表现差预警数量
+ //遍历计算总数
+ for (ResponseVo responseVo : performanceGroupCountList) {
+ //辖区id相同则数量相加
+ if (responseVo.getId().equals(id)){
+ count += performanceGroupCountList.get(i).getNum();
+ }
+ }
+
+
+ //2.保安员资格异常的数量
+ //遍历计算总数
+ for (ResponseVo responseVo : examinationGroupCountList) {
+ //辖区id相同则数量相加
+ if (responseVo.getId().equals(id)){
+ count += examinationGroupCountList.get(i).getNum();
+ }
+ }
+
+ //3.考试通过率低于50%
+ //遍历计算总数
+ for (ResponseVo responseVo : examPassingGroupCountList) {
+ //辖区id相同则数量相加
+ if (responseVo.getId().equals(id)){
+ count += examPassingGroupCountList.get(i).getNum();
+ }
+ }
+
+ //4.持证率低于50%
+ //遍历计算总数
+ for (ResponseVo responseVo : holdGroupCountList) {
+ //辖区id相同则数量相加
+ if (responseVo.getId().equals(id)){
+ count += holdGroupCountList.get(i).getNum();
+ }
+ }
+
+ //5.社保缴纳率低于50%
+ //遍历计算总数
+ for (ResponseVo responseVo : soidGroupCountList) {
+ //辖区id相同则数量相加
+ if (responseVo.getId().equals(id)){
+ count += soidGroupCountList.get(i).getNum();
+ }
+ }
+
+ //6.派遣率低于50%
+ //遍历计算总数
+ for (ResponseVo responseVo : dispatchGroupCountList) {
+ //辖区id相同则数量相加
+ if (responseVo.getId().equals(id)){
+ count += dispatchGroupCountList.get(i).getNum();
+ }
+ }
+
+ //封装数据
+ map.put("Count", count);
+ map.put("jurname", jurname);
+ map.put("id", id);
+ lists.add(map);
+ }
+ return R.data(lists);
+ }
}
diff --git a/src/main/java/org/springblade/modules/information/mapper/InformationMapper.java b/src/main/java/org/springblade/modules/information/mapper/InformationMapper.java
index 800c366..3100f3e 100644
--- a/src/main/java/org/springblade/modules/information/mapper/InformationMapper.java
+++ b/src/main/java/org/springblade/modules/information/mapper/InformationMapper.java
@@ -24,6 +24,8 @@
import org.springblade.modules.information.vo.InformationVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
+import org.springblade.modules.information.vo.ResponseVo;
+
import java.util.List;
import java.util.Map;
@@ -152,4 +154,29 @@
* @return
*/
List<InformationVO> getSecurityUnitServerLess2Page(IPage<InformationVO> page,@Param("information") InformationVO information);
+
+ /**
+ * 统计表现差的信息
+ * @return
+ */
+ List<ResponseVo> getSecurityPerformanceGroupCountList();
+
+ /**
+ * 统计资格审查异常信息
+ * @return
+ */
+ List<ResponseVo> getSecurityExaminationGroupCountList();
+
+ /**
+ * 考试通过率低于50%
+ * @return
+ */
+ List<ResponseVo> getSecurityExamPassingGroupCountList();
+
+ /**
+ * 查询 1 持证率低于50%, 2 社保缴纳率低于50%, 3 派遣率低于50%
+ * @param type 1 持证率低于50%, 2 社保缴纳率低于50%, 3 派遣率低于50%
+ * @return
+ */
+ List<ResponseVo> getSecurityHoldAndSoidAndDispatchGroupCountList(Integer type);
}
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 24e1aaa..99ea931 100644
--- a/src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml
+++ b/src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml
@@ -1622,12 +1622,23 @@
ON A.departmentid = D.dept_id
LEFT JOIN
(
- SELECT COUNT( * ) AS cznum, dept_id FROM blade_user where hold = 1 GROUP BY dept_id
+ SELECT COUNT( * ) AS cznum, dept_id FROM blade_user where
+ hold = 1
+ and role_id = '1412226235153731586'
+ and status= 1
+ and is_deleted = 0
+ GROUP BY dept_id
) E
ON A.departmentid = E.dept_id
LEFT JOIN
(
- SELECT COUNT( * ) AS pqnum, dept_id FROM blade_user where dispatch = 0 GROUP BY dept_id
+ SELECT COUNT( * ) AS pqnum, dept_id FROM blade_user
+ where
+ dispatch = 0
+ and role_id = '1412226235153731586'
+ and status= 1
+ and is_deleted = 0
+ GROUP BY dept_id
) F
ON A.departmentid = F.dept_id
where 1=1
@@ -2075,23 +2086,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>
@@ -2107,14 +2131,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})
@@ -2124,6 +2152,7 @@
</if>
</select>
+ <!-- and (sj.id = #{information.jurisdiction} or sj.parent_id = #{information.jurisdiction})-->
<!--根据辖区查询统计保安公司考试通过率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）-->
<select id="getSecurityUnitExamLess50Page" resultType="org.springblade.modules.information.vo.InformationVO">
@@ -2132,7 +2161,8 @@
si.*,
ifnull(a.count,0) znum,
ifnull(b.count,0) cnum,
- ifnull(FORMAT((b.count/c.count)*100,2),0) pnum
+ ifnull(FORMAT((b.count/c.count)*100,2),0) pnum,
+ ifnull(c.count,0) knum
from sys_information si
left join
(
@@ -2210,6 +2240,7 @@
d.jurisdiction = sj.id
where 1=1
and (d.stats = 2 or d.stats = 4)
+ and knum != 0
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})
@@ -2232,84 +2263,346 @@
and bu.`status` = 1
</sql>
- <!--根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，现实表现差，未缴纳社保，资格审查异常）-->
+ <!--根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，资格审查异常，考试通过率低于50%，持证率低于50%）-->
<select id="getSubstationWarnInfoPage" resultType="org.springblade.modules.information.vo.InformationVO">
- select jurisdictionName,enterprisename,detail from (
- (
+ (select
+ sj1.dept_name jurisdictionName,
+ si1.enterpriseName enterprisename,
+ "表现差" detail,
+ a.count znum
+ from (
select
- si.jurisdiction id,
- sj.parent_id,
- sj.dept_name jurisdictionName,
- si.enterprisename,
- "表现差" detail
+ bu.dept_id,count(*) count
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
- )
+ 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家的公司数量的分页信息-->
@@ -2355,6 +2648,272 @@
and b.departmentid = #{information.departmentid}
</if>
</select>
+
+ <!--统计表现差的信息-->
+ <select id="getSecurityPerformanceGroupCountList" resultType="org.springblade.modules.information.vo.ResponseVo">
+ select a.id,
+ a.dept_name name,
+ ifnull(b.count, 0) num
+ from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) a
+ left join
+ (
+ select sj.id,
+ sj.parent_id,
+ count(*) count
+ 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
+ group by si.jurisdiction
+ ) b
+ on
+ a.id = b.parent_id
+ </select>
+
+
+ <!--统计资格审查异常信息-->
+ <select id="getSecurityExaminationGroupCountList" resultType="org.springblade.modules.information.vo.ResponseVo">
+ select a.id, a.dept_name name, ifnull(b.count, 0) num
+ from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) a
+ left join
+ (
+ select sj.id,
+ sj.parent_id,
+ count(*) count
+ from
+ blade_user bu
+ 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 bu.examination_type = 1
+ group by si.jurisdiction
+ ) b
+ on
+ a.id = b.parent_id
+ </select>
+
+
+ <!--考试通过率低于50%-->
+ <select id="getSecurityExamPassingGroupCountList" resultType="org.springblade.modules.information.vo.ResponseVo">
+ select g.id, g.dept_name name, ifnull(f.count, 0) num
+ from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) g
+ left join
+ (
+ select e.id, e.parent_id, count(*) count
+ from (
+ select d.*, ifnull(FORMAT((num2/num1)*100, 2), 0) num3, sj.* from (
+ select a.dept_id, ifnull(b.count, 0) num1, ifnull(c.count, 0) num2 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 a.dept_id = c.dept_id
+ ) d
+ left join
+ sys_information si
+ on
+ si.departmentid = d.dept_id
+ left join
+ sys_jurisdiction sj
+ on
+ sj.id = si.jurisdiction
+ where d.num1!=0
+ and (stats = 2 or stats=4)
+ ) e
+ where e.num3 < 50
+ group by e.id
+ ) f
+ on
+ g.id = f.parent_id
+ </select>
+
+ <!--持证率低于50%,社保缴纳率低于50%,派遣率低于50%-->
+ <!-- <select id="getSecurityHoldAndSoidAndDispatchGroupCountList" resultType="org.springblade.modules.information.vo.ResponseVo">-->
+ <!-- select g.id,g.dept_name name,ifnull(f.count,0) num-->
+ <!-- from-->
+ <!-- (select id,dept_name from sys_jurisdiction where parent_id = 1372091709474910209) g-->
+ <!-- left join-->
+ <!-- (-->
+ <!-- select e.id,e.parent_id,count(*) count from (-->
+ <!-- select d.*,ifnull(FORMAT((num2/num1)*100,2),0) num3,sj.* from (-->
+ <!-- select a.dept_id,ifnull(a.count,0) num1,ifnull(b.count,0) num2 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-->
+ <!-- )d-->
+ <!-- left join-->
+ <!-- sys_information si-->
+ <!-- on-->
+ <!-- si.departmentid = d.dept_id-->
+ <!-- left join-->
+ <!-- sys_jurisdiction sj-->
+ <!-- on-->
+ <!-- sj.id = si.jurisdiction-->
+ <!-- and (stats = 2 or stats=4)-->
+ <!-- )e-->
+ <!-- where e.num3 < 50-->
+ <!-- group by e.id-->
+ <!-- ) f-->
+ <!-- on-->
+ <!-- g.id = f.parent_id-->
+ <!-- </select>-->
+
+
+ <!--持证率低于50%,社保缴纳率低于50%,派遣率低于50%-->
+ <select id="getSecurityHoldAndSoidAndDispatchGroupCountList" resultType="org.springblade.modules.information.vo.ResponseVo">
+ select g.id,g.dept_name name,ifnull(f.count,0) num
+ from
+ (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
+ (
+ 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
+ </select>
+
+
<select id="seleP" resultType="java.util.HashMap">
SELECT
IFNULL(G.pnum, 0) AS pnum,
diff --git a/src/main/java/org/springblade/modules/information/service/IInformationService.java b/src/main/java/org/springblade/modules/information/service/IInformationService.java
index 704f66a..f753d70 100644
--- a/src/main/java/org/springblade/modules/information/service/IInformationService.java
+++ b/src/main/java/org/springblade/modules/information/service/IInformationService.java
@@ -22,6 +22,7 @@
import org.springblade.modules.information.vo.InformationVO;
import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.core.metadata.IPage;
+import org.springblade.modules.information.vo.ResponseVo;
import java.util.List;
import java.util.Map;
@@ -177,4 +178,29 @@
*/
List<Map<Object,Object>> selectBxs(String jurisdiction);
List<Map<String, Object>> seleP(String jurisdiction,String deptid);
+
+ /**
+ * 统计表现差的信息
+ * @return
+ */
+ List<ResponseVo> getSecurityPerformanceGroupCountList();
+
+ /**
+ * 统计资格审查异常信息
+ * @return
+ */
+ List<ResponseVo> getSecurityExaminationGroupCountList();
+
+ /**
+ * 考试通过率低于50%
+ * @return
+ */
+ List<ResponseVo> getSecurityExamPassingGroupCountList();
+
+ /**
+ * 查询 1 持证率低于50%, 2 社保缴纳率低于50%, 3 派遣率低于50%
+ * @param type 1 持证率低于50%, 2 社保缴纳率低于50%, 3 派遣率低于50%
+ * @return
+ */
+ List<ResponseVo> getSecurityHoldAndSoidAndDispatchGroupCountList(Integer type);
}
diff --git a/src/main/java/org/springblade/modules/information/service/impl/InformationServiceImpl.java b/src/main/java/org/springblade/modules/information/service/impl/InformationServiceImpl.java
index 843fcc3..127d786 100644
--- a/src/main/java/org/springblade/modules/information/service/impl/InformationServiceImpl.java
+++ b/src/main/java/org/springblade/modules/information/service/impl/InformationServiceImpl.java
@@ -22,6 +22,7 @@
import org.springblade.modules.information.mapper.InformationMapper;
import org.springblade.modules.information.service.IInformationService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
+import org.springblade.modules.information.vo.ResponseVo;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -404,5 +405,40 @@
return baseMapper.seleP(jurisdiction, deptid);
}
+ /**
+ * 统计表现差的信息
+ * @return
+ */
+ @Override
+ public List<ResponseVo> getSecurityPerformanceGroupCountList() {
+ return baseMapper.getSecurityPerformanceGroupCountList();
+ }
+ /**
+ * 统计资格审查异常信息
+ * @return
+ */
+ @Override
+ public List<ResponseVo> getSecurityExaminationGroupCountList() {
+ return baseMapper.getSecurityExaminationGroupCountList();
+ }
+
+ /**
+ * 考试通过率低于50%
+ * @return
+ */
+ @Override
+ public List<ResponseVo> getSecurityExamPassingGroupCountList() {
+ return baseMapper.getSecurityExamPassingGroupCountList();
+ }
+
+ /**
+ * 查询 1 持证率低于50%, 2 社保缴纳率低于50%, 3 派遣率低于50%
+ * @param type 1 持证率低于50%, 2 社保缴纳率低于50%, 3 派遣率低于50%
+ * @return
+ */
+ @Override
+ public List<ResponseVo> getSecurityHoldAndSoidAndDispatchGroupCountList(Integer type) {
+ return baseMapper.getSecurityHoldAndSoidAndDispatchGroupCountList(type);
+ }
}
diff --git a/src/main/java/org/springblade/modules/information/vo/InformationVO.java b/src/main/java/org/springblade/modules/information/vo/InformationVO.java
index a5504fa..5e2ef08 100644
--- a/src/main/java/org/springblade/modules/information/vo/InformationVO.java
+++ b/src/main/java/org/springblade/modules/information/vo/InformationVO.java
@@ -57,4 +57,6 @@
* 占比
*/
private Integer proportion;
+
+ private Integer knum;
}
diff --git a/src/main/java/org/springblade/modules/information/vo/ResponseVo.java b/src/main/java/org/springblade/modules/information/vo/ResponseVo.java
new file mode 100644
index 0000000..9bf7023
--- /dev/null
+++ b/src/main/java/org/springblade/modules/information/vo/ResponseVo.java
@@ -0,0 +1,21 @@
+package org.springblade.modules.information.vo;
+
+import lombok.Data;
+
+import java.io.Serializable;
+
+/**
+ * 统计返回结果
+ * @author zhongrj
+ * @since 2021-9-17
+ */
+@Data
+public class ResponseVo implements Serializable {
+
+ private String id;
+
+ private String name;
+
+ private Integer num;
+
+}
--
Gitblit v1.9.3