From 50fdcb14bcd0ba57181d434a96d7d416edf1c29d Mon Sep 17 00:00:00 2001
From: Administrator <admin>
Date: Fri, 17 Sep 2021 10:44:29 +0800
Subject: [PATCH] 考试，通知公告，派遣记录，单位，保安公司统计接口同步
---
src/main/java/org/springblade/modules/dispatcher/vo/DispatcherUnitVO.java | 8
src/main/java/org/springblade/modules/dispatcher/vo/DispatcherVO.java | 16 +
src/main/java/org/springblade/modules/desk/service/impl/NoticeServiceImpl.java | 8
src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherUnitMapper.xml | 25 +
src/main/java/org/springblade/modules/apply/mapper/ExamPaymentMapper.xml | 1
src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml | 454 +++++++++++++++++++++++++++
src/main/java/org/springblade/modules/information/service/IInformationService.java | 78 ++++
src/main/java/org/springblade/modules/information/controller/InformationsController.java | 136 ++++++++
src/main/java/org/springblade/modules/information/mapper/InformationMapper.java | 58 +++
src/main/java/org/springblade/modules/information/service/impl/InformationServiceImpl.java | 108 ++++++
src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherMapper.xml | 9
11 files changed, 884 insertions(+), 17 deletions(-)
diff --git a/src/main/java/org/springblade/modules/apply/mapper/ExamPaymentMapper.xml b/src/main/java/org/springblade/modules/apply/mapper/ExamPaymentMapper.xml
index a4bdefe..e3e2358 100644
--- a/src/main/java/org/springblade/modules/apply/mapper/ExamPaymentMapper.xml
+++ b/src/main/java/org/springblade/modules/apply/mapper/ExamPaymentMapper.xml
@@ -47,6 +47,5 @@
<if test="examPayment.id!=null and examPayment.id!=''">
and id = #{examPayment.id}
</if>
-
</select>
</mapper>
diff --git a/src/main/java/org/springblade/modules/desk/service/impl/NoticeServiceImpl.java b/src/main/java/org/springblade/modules/desk/service/impl/NoticeServiceImpl.java
index bb6413b..6155d84 100644
--- a/src/main/java/org/springblade/modules/desk/service/impl/NoticeServiceImpl.java
+++ b/src/main/java/org/springblade/modules/desk/service/impl/NoticeServiceImpl.java
@@ -17,13 +17,18 @@
package org.springblade.modules.desk.service.impl;
import com.baomidou.mybatisplus.core.metadata.IPage;
+import lombok.AllArgsConstructor;
import org.springblade.core.mp.base.BaseServiceImpl;
import org.springblade.core.secure.utils.AuthUtil;
import org.springblade.modules.desk.entity.Notice;
import org.springblade.modules.desk.mapper.NoticeMapper;
import org.springblade.modules.desk.service.INoticeService;
import org.springblade.modules.desk.vo.NoticeVO;
+import org.springblade.modules.system.entity.User;
+import org.springblade.modules.system.service.IUserService;
import org.springframework.stereotype.Service;
+
+import java.util.List;
/**
* 服务实现类
@@ -31,8 +36,11 @@
* @author Chill
*/
@Service
+@AllArgsConstructor
public class NoticeServiceImpl extends BaseServiceImpl<NoticeMapper, Notice> implements INoticeService {
+ private final IUserService userService;
+
@Override
public IPage<NoticeVO> selectNoticePage(IPage<NoticeVO> page, NoticeVO notice) {
// 若不使用mybatis-plus自带的分页方法，则不会自动带入tenantId，所以我们需要自行注入
diff --git a/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherMapper.xml b/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherMapper.xml
index 942e603..cb2d2d4 100644
--- a/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherMapper.xml
+++ b/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherMapper.xml
@@ -16,7 +16,10 @@
<!--派遣记录分页数据-->
<select id="selectDispatcherPage" resultType="org.springblade.modules.dispatcher.vo.DispatcherVO">
select
- sd.*,bu.real_name securityName,sdu.name dispatcherCompany,bu.cardid idCardNo
+ sd.*,
+ bu.real_name securityName,sdu.name dispatcherCompany,bu.cardid idCardNo,bu.phone securityPhone,
+ bd.dept_name deptName,
+ sdu.linkman,sdu.phone
from
sys_dispatcher sd
left join
@@ -27,6 +30,10 @@
blade_user bu
on
bu.id = sd.user_ids
+ left join
+ blade_dept bd
+ on
+ bd.id = bu.dept_id
where 1=1
<if test="dispatcher.dispatcherUnitId!=null">
and sd.dispatcher_unit_id = #{dispatcher.dispatcherUnitId}
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 fa68e96..0bd7c8e 100644
--- a/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherUnitMapper.xml
+++ b/src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherUnitMapper.xml
@@ -5,15 +5,23 @@
<!--派遣公司分页信息-->
<select id="selectDispatcherUnitPage" resultType="org.springblade.modules.dispatcher.vo.DispatcherUnitVO">
select
- sdu.*,bt.dept_name tenantName
+ sdu.*,bt.dept_name tenantName,bu.real_name realName
from
- sys_dispatcher_unit sdu
+ sys_dispatcher_unit sdu
LEFT JOIN
- blade_dept bt
+ blade_dept bt
ON
- sdu.dept_id = bt.id
+ sdu.dept_id = bt.id
+ left join
+ sys_dispatcher sd
+ on
+ sdu.id = sd.dispatcher_unit_id
+ left join
+ blade_user bu
+ on
+ sd.user_ids = bu.id
WHERE
- 1=1
+ 1=1
<if test="dispatcherUnit.tenantName!=null and dispatcherUnit.tenantName!=''">
and bt.dept_name like concat('%', #{dispatcherUnit.tenantName},'%')
</if>
@@ -41,6 +49,13 @@
<if test="dispatcherUnit.district!=null">
and sdu.district = #{dispatcherUnit.district}
</if>
+ <if test="dispatcherUnit.isExpire==1">
+ and sdu.start_time <= now()
+ and sdu.end_time > now()
+ </if>
+ <if test="dispatcherUnit.isExpire==2">
+ and sdu.end_time <= now()
+ </if>
</select>
<!--自定义树-->
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 32abc87..cc762c9 100644
--- a/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherUnitVO.java
+++ b/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherUnitVO.java
@@ -32,7 +32,7 @@
private static final long serialVersionUID = 1L;
/**
- * 保安公司名称
+ * 保安公司名称
*/
private String tenantName;
@@ -40,4 +40,10 @@
* 派遣单位名称
*/
private String dispatcherUnitName;
+
+
+ /**
+ * 合同是否到期 1：否 2：是
+ */
+ private Integer isExpire;
}
diff --git a/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherVO.java b/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherVO.java
index f566fd3..d5d28e0 100644
--- a/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherVO.java
+++ b/src/main/java/org/springblade/modules/dispatcher/vo/DispatcherVO.java
@@ -69,4 +69,20 @@
*/
private String dispatch;
+ /**
+ * 联系人
+ */
+ private String linkman;
+
+ /**
+ * 联系电话
+ */
+ private String phone;
+
+
+ /**
+ * 联系电话
+ */
+ private String securityPhone;
+
}
diff --git a/src/main/java/org/springblade/modules/information/controller/InformationsController.java b/src/main/java/org/springblade/modules/information/controller/InformationsController.java
new file mode 100644
index 0000000..a72081d
--- /dev/null
+++ b/src/main/java/org/springblade/modules/information/controller/InformationsController.java
@@ -0,0 +1,136 @@
+package org.springblade.modules.information.controller;
+
+import com.baomidou.mybatisplus.core.metadata.IPage;
+import lombok.AllArgsConstructor;
+import org.springblade.core.mp.support.Condition;
+import org.springblade.core.mp.support.Query;
+import org.springblade.core.tool.api.R;
+import org.springblade.modules.information.service.IInformationService;
+import org.springblade.modules.information.vo.InformationVO;
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RestController;
+
+/**
+ * 组织机构详情控制层 2
+ * @author zhongrj
+ * @time 2021-09-14
+ */
+@RestController
+@RequestMapping("/information")
+@AllArgsConstructor
+public class InformationsController {
+
+ private final IInformationService informationService;
+
+ /**
+ * 获取保安公司信息（本市保安公司，分公司）包含经纬度
+ * @return
+ */
+ @GetMapping("/getSecurityUnitPositionInfo")
+ public R getSecurityUnitPositionInfo(){
+ return R.data(informationService.getSecurityUnitPositionInfo());
+ }
+
+
+ /**
+ * 根据辖区查询保安公司分页信息（本市保安公司，分公司）
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSecurityUnitInfoPage")
+ public R<IPage<InformationVO>> getSecurityUnitInfoPage(InformationVO information, Query query){
+ return R.data(informationService.getSecurityUnitInfoPage(Condition.getPage(query), information));
+ }
+
+
+ /**
+ * 根据辖区查询统计保安公司持证情况分页信息（本市保安公司，分公司）
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSecurityUnitHoldInfoPage")
+ public R<IPage<InformationVO>> getSecurityUnitHoldInfoPage(InformationVO information, Query query){
+ return R.data(informationService.getSecurityUnitHoldInfoPage(Condition.getPage(query), information));
+ }
+
+
+ /**
+ * 根据辖区查询统计保安公司持证率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSecurityUnitHoldLessPage")
+ public R<IPage<InformationVO>> getSecurityUnitHoldLessPage(InformationVO information, Query query){
+ return R.data(informationService.getSecurityUnitHoldLess50Page(Condition.getPage(query), information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司派遣率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSecurityUnitDispatcherLessPage")
+ public R<IPage<InformationVO>> getSecurityUnitDispatcherLessPage(InformationVO information, Query query){
+ return R.data(informationService.getSecurityUnitDispatcherLess50Page(Condition.getPage(query), information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司社保缴纳率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSecurityUnitSoilLessPage")
+ public R<IPage<InformationVO>> getSecurityUnitSoilLessPage(InformationVO information, Query query){
+ return R.data(informationService.getSecurityUnitSoilLess50Page(Condition.getPage(query), information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司保险缴纳率低于90%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSecurityUnitInsuranceLessPage")
+ public R<IPage<InformationVO>> getSecurityUnitInsuranceLessPage(InformationVO information, Query query){
+ return R.data(informationService.getSecurityUnitInsuranceLessPage(Condition.getPage(query), information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司考试通过率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSecurityUnitExamLessPage")
+ public R<IPage<InformationVO>> getSecurityUnitExamLessPage(InformationVO information, Query query){
+ return R.data(informationService.getSecurityUnitExamLess50Page(Condition.getPage(query), information));
+ }
+
+ /**
+ * 根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，现实表现差，未缴纳社保，资格审查异常）
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSubstationWarnInfoPage")
+ public R<IPage<InformationVO>> getSubstationWarnInfoPage(InformationVO information, Query query){
+ return R.data(informationService.getSubstationWarnInfoPage(Condition.getPage(query), information));
+ }
+
+ /**
+ * 根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司））服务对象小于2家的公司数量的分页信息
+ * @param query 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @GetMapping("/getSecurityUnitServerLessPage")
+ public R<IPage<InformationVO>> getSecurityUnitServerLessPage(InformationVO information, Query query){
+ return R.data(informationService.getSecurityUnitServerLess2Page(Condition.getPage(query), information));
+ }
+}
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 3c52ed7..2a1902e 100644
--- a/src/main/java/org/springblade/modules/information/mapper/InformationMapper.java
+++ b/src/main/java/org/springblade/modules/information/mapper/InformationMapper.java
@@ -16,6 +16,7 @@
*/
package org.springblade.modules.information.mapper;
+import com.baomidou.mybatisplus.annotation.SqlParser;
import liquibase.pro.packaged.S;
import org.apache.ibatis.annotations.Param;
import org.springblade.modules.dispatcher.vo.DispatcherVO;
@@ -92,4 +93,61 @@
Map<String, String> queryYearBanan(String year,String jurisdiction,String deptid);
Map<String, String> queryYearDe(String year,String jurisdiction,String deptid);
void deleteDept(String ids);
+
+ /**
+ * 获取保安公司信息（本市保安公司，分公司）包含经纬度
+ * @return
+ */
+ List<InformationVO> getSecurityUnitPositionInfo();
+
+ /**
+ * 根据辖区查询保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ List<InformationVO> getSecurityUnitInfoPage(IPage<InformationVO> page,@Param("information") InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司持证情况分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ List<InformationVO> getSecurityUnitHoldInfoPage(IPage<InformationVO> page,@Param("information") InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司(1:持证率低于50%, 2:派遣率低于50% 3：缴纳社保率低于50% 4：保险缴纳 的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ List<InformationVO> getSecurityUnitLess50Page(IPage<InformationVO> page,
+ @Param("type") Integer type,
+ @Param("information") InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司考试通过率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ List<InformationVO> getSecurityUnitExamLess50Page(IPage<InformationVO> page,@Param("information") InformationVO information);
+
+ /**
+ * 根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，现实表现差，未缴纳社保，资格审查异常）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @SqlParser(filter = true)
+ List<InformationVO> getSubstationWarnInfoPage(IPage<InformationVO> page,@Param("information") InformationVO information);
+
+ /**
+ * 根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司））服务对象小于2家的公司数量的分页信息
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ List<InformationVO> getSecurityUnitServerLess2Page(IPage<InformationVO> page,@Param("information") InformationVO information);
}
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 57e3da1..fe2149a 100644
--- a/src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml
+++ b/src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml
@@ -1079,6 +1079,8 @@
IFNULL( D.wpaiqnum, 0 ) AS wpaiqnum,
IFNULL( E.sbnum, 0 ) AS sbnum,
IFNULL( F.wcaijnum, 0 ) AS wcaijnum,
+ IFNULL( H.ycaijzpnum, 0 ) AS ycaijzpnum,
+ IFNULL( I.ycaijzwnum, 0 ) AS ycaijzwnum,
IFNULL( G.yicnum, 0 ) AS yicnum,
A.dept_id,
d.enterpriseName AS deptname,
@@ -1132,8 +1134,10 @@
) D ON D.dept_id = A.dept_id
LEFT JOIN (
SELECT COUNT( * ) AS sbnum, deptid, jurisdiction FROM sys_socil GROUP BY deptid, jurisdiction
- ) E ON E.deptid = A.dept_id
- LEFT JOIN (
+ ) E
+ ON E.deptid = A.dept_id
+ LEFT JOIN
+ (
SELECT
COUNT( * ) AS wcaijnum,
dept_id,
@@ -1146,7 +1150,40 @@
GROUP BY
dept_id,
jurisdiction
- ) F ON F.dept_id = A.dept_id
+ ) F
+ ON F.dept_id = A.dept_id
+ LEFT JOIN
+ (
+ SELECT
+ COUNT( * ) AS ycaijzpnum,
+ dept_id,
+ jurisdiction
+ FROM
+ blade_user
+ WHERE
+ role_id = '1412226235153731586' and status=1 AND is_deleted=0
+ AND photo=0
+ GROUP BY
+ dept_id,
+ jurisdiction
+ ) H
+ ON H.dept_id = A.dept_id
+ LEFT JOIN
+ (
+ SELECT
+ COUNT( * ) AS ycaijzwnum,
+ dept_id,
+ jurisdiction
+ FROM
+ blade_user
+ WHERE
+ role_id = '1412226235153731586' and status=1 AND is_deleted=0
+ AND fingerprint=0
+ GROUP BY
+ dept_id,
+ jurisdiction
+ ) I
+ ON I.dept_id = A.dept_id
LEFT JOIN (
SELECT
COUNT( * ) AS yicnum,
@@ -1387,7 +1424,10 @@
A.jurisdiction,
A.dept_name as jurname,
IFNULL( B.num, 0 ) AS fwnum,
- IFNULL( C.znum, 0 ) AS znum
+ IFNULL( C.znum, 0 ) AS znum,
+ IFNULL( D.dqnum, 0 ) AS dqnum,
+ IFNULL( E.cznum, 0 ) AS cznum,
+ IFNULL( F.pqnum, 0 ) AS pqnum
FROM
(
SELECT
@@ -1402,11 +1442,36 @@
WHERE
i.enterpriseName IS NOT NULL
) A
- LEFT JOIN ( SELECT COUNT( * ) AS num, dept_id FROM sys_dispatcher_unit GROUP BY dept_id ) B ON A.departmentid =
- B.dept_id
- LEFT JOIN ( SELECT COUNT( * ) AS znum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' and
- status=1 GROUP BY
- dept_id ) C ON A.departmentid = C.dept_id
+ LEFT JOIN
+ (
+ SELECT COUNT( * ) AS num, dept_id FROM sys_dispatcher_unit
+ where
+ start_time <= now() and end_time > now()
+ GROUP BY dept_id
+ ) B
+ ON A.departmentid = B.dept_id
+ LEFT JOIN
+ (
+ SELECT COUNT( * ) AS znum, dept_id FROM blade_user WHERE role_id = '1412226235153731586'
+ and status=1
+ GROUP BY dept_id
+ ) C
+ ON A.departmentid = C.dept_id
+ LEFT JOIN
+ (
+ SELECT COUNT( * ) AS dqnum, dept_id FROM sys_dispatcher_unit where end_time < now() GROUP BY dept_id
+ ) D
+ ON A.departmentid = D.dept_id
+ LEFT JOIN
+ (
+ SELECT COUNT( * ) AS cznum, dept_id FROM blade_user where hold = 1 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
+ ) F
+ ON A.departmentid = F.dept_id
where 1=1
<if test="jurisdiction!=null and jurisdiction!=''and jurisdiction!='1372091709474910209'">
AND A.jurisdiction in(${jurisdiction})
@@ -1765,4 +1830,375 @@
) = 1
AND parent_id!=0
</select>
+
+ <!--获取保安公司信息（本市保安公司，分公司）包含经纬度-->
+ <select id="getSecurityUnitPositionInfo" resultType="org.springblade.modules.information.vo.InformationVO">
+ select
+ si.*,sj.dept_name jurisdictionName
+ from
+ sys_information si
+ left join
+ sys_jurisdiction sj
+ on
+ si.jurisdiction = sj.id
+ where 1=1
+ and (stats = 2 or stats = 4)
+ </select>
+
+ <!--根据辖区查询保安公司分页信息（本市保安公司，分公司）-->
+ <select id="getSecurityUnitInfoPage" resultType="org.springblade.modules.information.vo.InformationVO">
+ select
+ si.*,sj.dept_name jurisdictionName
+ from
+ sys_information si
+ left join
+ sys_jurisdiction sj
+ on
+ si.jurisdiction = sj.id
+ where 1=1
+ and (stats = 0 or stats = 1 or stats = 2 or stats = 4)
+ <if test="information.jurisdiction!=null and information.jurisdiction!=''">
+ and (sj.id = #{information.jurisdiction} or sj.parent_id = #{information.jurisdiction})
+ </if>
+ </select>
+
+ <sql id="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
+ </sql>
+
+ <!--根据辖区查询统计保安公司持证情况分页信息（本市保安公司，分公司）-->
+ <select id="getSecurityUnitHoldInfoPage" resultType="org.springblade.modules.information.vo.InformationVO">
+ select
+ si.*,sj.dept_name jurisdictionName,
+ ifnull(a.count,0) znum,
+ ifnull(b.count,0) cnum,
+ ifnull(c.count,0) pnum
+ from sys_information si
+ left join
+ (
+ <include refid="getSecurity"/>
+ group by dept_id
+ ) a
+ on
+ a.dept_id = si.departmentid
+ left join
+ (
+ <include refid="getSecurity"/>
+ and bu.hold = 1
+ group by dept_id
+ ) b
+ on
+ b.dept_id = si.departmentid
+ left join
+ (
+ <include refid="getSecurity"/>
+ and bu.hold = 2
+ group by dept_id
+ ) c
+ on
+ c.dept_id = si.departmentid
+ left join
+ sys_jurisdiction sj
+ on
+ si.jurisdiction = sj.id
+ where 1=1
+ and (si.stats = 2 or si.stats = 4)
+ <if test="information.jurisdiction!=null and information.jurisdiction!=''">
+ and (sj.id = #{information.jurisdiction} or sj.parent_id = #{information.jurisdiction})
+ </if>
+ </select>
+
+ <!--根据辖区查询统计保安公司(1:持证率低于50% 2:派遣率低于50% 3：缴纳社保率低于50% 的保安公司个数及保安公司分页信息（本市保安公司，分公司）-->
+ <select id="getSecurityUnitLess50Page" resultType="org.springblade.modules.information.vo.InformationVO">
+ select c.*,sj.dept_name jurisdictionName from (
+ select
+ si.*,
+ 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
+ (
+ <include refid="getSecurity"/>
+ group by dept_id
+ ) a
+ on
+ a.dept_id = si.departmentid
+ left join
+ (
+ <include refid="getSecurity"/>
+ <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>
+ <if test="type==4">
+ and bu.insurance = 0
+ </if>
+ group by dept_id
+ ) b
+ on
+ b.dept_id = si.departmentid
+ )c
+ left join
+ sys_jurisdiction sj
+ on
+ c.jurisdiction = sj.id
+ where 1=1
+ and (c.stats = 2 or c.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})
+ </if>
+ <if test="information.departmentid!=null and information.departmentid!=''">
+ and c.departmentid = #{information.departmentid}
+ </if>
+ </select>
+
+
+ <!--根据辖区查询统计保安公司考试通过率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）-->
+ <select id="getSecurityUnitExamLess50Page" resultType="org.springblade.modules.information.vo.InformationVO">
+ select d.*,sj.dept_name jurisdictionName from (
+ select
+ si.*,
+ ifnull(a.count,0) znum,
+ ifnull(b.count,0) cnum,
+ ifnull(FORMAT((b.count/c.count)*100,2),0) pnum
+ from sys_information si
+ left join
+ (
+ <include refid="getSecurity"/>
+ group by dept_id
+ ) a
+ on
+ a.dept_id = si.departmentid
+ left join
+ (
+ select b1.dept_id,ifnull(b2.count,0) count 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
+ ) b1
+
+ 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
+ ) b2
+ on
+ b1.dept_id = b2.dept_id
+ ) b
+ on
+ b.dept_id = si.departmentid
+ left join
+ (
+ select c1.dept_id,ifnull(c2.count,0) count 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
+ ) c1
+
+ 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
+ ) c2
+ on
+ c1.dept_id = c2.dept_id
+ ) c
+ on
+ c.dept_id = si.departmentid
+ )d
+ left join
+ sys_jurisdiction sj
+ on
+ d.jurisdiction = sj.id
+ where 1=1
+ and (d.stats = 2 or d.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})
+ </if>
+ </select>
+
+ <sql id="getSubstationWarn">
+ left join
+ sys_information si
+ on
+ si.departmentid = bu.dept_id
+ 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
+ </sql>
+
+ <!--根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，现实表现差，未缴纳社保，资格审查异常）-->
+ <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
+ )
+ ) a
+ where 1=1
+ <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
+ and (a.id = #{information.jurisdiction} or a.parent_id = #{information.jurisdiction})
+ </if>
+ </select>
+
+ <!--根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司））服务对象小于2家的公司数量的分页信息-->
+ <select id="getSecurityUnitServerLess2Page" resultType="org.springblade.modules.information.vo.InformationVO">
+ select * from
+ (
+ select
+ DISTINCT
+ si.id,
+ si.departmentid,
+ si.jurisdiction,
+ si.enterprisename,
+ sj.dept_name jurisdictionName,
+ ifnull(a.count,0) znum,
+ sdu.name dispatchUnitName,
+ sdu.end_time
+ from sys_information si
+ left join
+ sys_dispatcher_unit sdu
+ on
+ si.departmentid = sdu.dept_id
+ left join
+ sys_jurisdiction sj
+ on
+ sj.id = si.jurisdiction
+ left join
+ (
+ select ifnull(count(*),0) count,dept_id from sys_dispatcher_unit
+ where 1=1
+ and start_time <= now()
+ and end_time > now()
+ GROUP BY dept_id
+ ) a
+ on
+ a.dept_id = si.departmentid
+ where 1=1
+ <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
+ and (sj.id = #{information.jurisdiction} or sj.parent_id = #{information.jurisdiction})
+ </if>
+ ) b
+ where b.znum < 2
+ <if test="information.departmentid!=null and information.departmentid!=''">
+ and b.departmentid = #{information.departmentid}
+ </if>
+ </select>
+
+
+
</mapper>
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 d4b2bd1..649b79f 100644
--- a/src/main/java/org/springblade/modules/information/service/IInformationService.java
+++ b/src/main/java/org/springblade/modules/information/service/IInformationService.java
@@ -91,4 +91,82 @@
List<Map<Object,Object>> selJur();
String selJurchilder(String id);
List<Map<Object,Object>> seleJuList(String substring);
+
+ /**
+ * 获取保安公司信息（本市保安公司，分公司）包含经纬度
+ * @return
+ */
+ List<InformationVO> getSecurityUnitPositionInfo();
+
+ /**
+ * 根据辖区查询保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSecurityUnitInfoPage(IPage<InformationVO> page, InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司持证情况分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSecurityUnitHoldInfoPage(IPage<InformationVO> page, InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司持证率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSecurityUnitHoldLess50Page(IPage<InformationVO> page, InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司派遣率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSecurityUnitDispatcherLess50Page(IPage<InformationVO> page, InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司社保缴纳率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSecurityUnitSoilLess50Page(IPage<InformationVO> page, InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司考试通过率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSecurityUnitExamLess50Page(IPage<InformationVO> page, InformationVO information);
+
+ /**
+ * 根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，现实表现差，未缴纳社保，资格审查异常）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSubstationWarnInfoPage(IPage<InformationVO> page, InformationVO information);
+
+ /**
+ * 根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司））服务对象小于2家的公司数量的分页信息
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSecurityUnitServerLess2Page(IPage<InformationVO> page, InformationVO information);
+
+ /**
+ * 根据辖区查询统计保安公司保险缴纳率低于90%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ IPage<InformationVO> getSecurityUnitInsuranceLessPage(IPage<InformationVO> page, InformationVO information);
}
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 a1ca48d..310c143 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
@@ -286,5 +286,113 @@
return baseMapper.seleJuList(substring);
}
+ /**
+ * 获取保安公司信息（本市保安公司，分公司）包含经纬度
+ * @return
+ */
+ @Override
+ public List<InformationVO> getSecurityUnitPositionInfo() {
+ return baseMapper.getSecurityUnitPositionInfo();
+ }
+
+ /**
+ * 根据辖区查询保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSecurityUnitInfoPage(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSecurityUnitInfoPage(page, information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司持证情况分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSecurityUnitHoldInfoPage(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSecurityUnitHoldInfoPage(page, information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司持证率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSecurityUnitHoldLess50Page(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSecurityUnitLess50Page(page,1,information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司派遣率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSecurityUnitDispatcherLess50Page(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSecurityUnitLess50Page(page,2, information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司社保缴纳率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSecurityUnitSoilLess50Page(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSecurityUnitLess50Page(page,3, information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司考试通过率低于50%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSecurityUnitExamLess50Page(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSecurityUnitExamLess50Page(page, information));
+ }
+
+ /**
+ * 根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，现实表现差，未缴纳社保，资格审查异常）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSubstationWarnInfoPage(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSubstationWarnInfoPage(page, information));
+ }
+
+ /**
+ * 根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司））服务对象小于2家的公司数量的分页信息
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSecurityUnitServerLess2Page(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSecurityUnitServerLess2Page(page, information));
+ }
+
+ /**
+ * 根据辖区查询统计保安公司保险缴纳率低于90%的保安公司个数及保安公司分页信息（本市保安公司，分公司）
+ * @param page 分页条件
+ * @param information 查询条件
+ * @return
+ */
+ @Override
+ public IPage<InformationVO> getSecurityUnitInsuranceLessPage(IPage<InformationVO> page, InformationVO information) {
+ return page.setRecords(baseMapper.getSecurityUnitLess50Page(page,4, information));
+ }
+
}
--
Gitblit v1.9.3