src/main/java/org/springblade/modules/information/controller/InformationController.java
@@ -34,12 +34,15 @@ import org.springblade.core.mp.support.Query; import org.springblade.core.tool.api.R; import org.springblade.core.tool.support.Kv; import org.springblade.core.tool.utils.DateUtil; import org.springblade.core.tool.utils.DigestUtil; import org.springblade.core.tool.utils.Func; import org.springblade.core.tool.utils.StringUtil; import org.springblade.modules.FTP.FtpUtil; import org.springblade.modules.FTP.OutJson; import org.springblade.modules.information.entity.Information; import org.springblade.modules.information.excel.ExportInformationExcel; import org.springblade.modules.information.excel.ExportInformationSecurityStatistics; import org.springblade.modules.information.excel.InforExcel; import org.springblade.modules.information.excel.InforImporter; import org.springblade.modules.information.service.IInformationService; @@ -54,6 +57,7 @@ import org.springframework.web.bind.annotation.*; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import javax.validation.Valid; import java.io.*; import java.text.DateFormat; @@ -483,4 +487,40 @@ return sortedMap; } /** * 业务统计情况 */ @GetMapping("/selectBusinessStatistics") public R<IPage> selectBusinessStatistics(InformationVO information, Query query) { IPage list = informationService.selectBusinessStatistics(Condition.getPage(query), information); return R.data(list); } /** * 保安员情况统计 */ @GetMapping("/selectSecurityStatistics") public R<IPage> selectSecurityStatistics(InformationVO information, Query query) { IPage list = informationService.selectSecurityStatistics(Condition.getPage(query), information); return R.data(list); } /** * 业务统计情况导出 */ @GetMapping("/export-business-statis") public void exportBusinessStatis(HttpServletResponse response, InformationVO information) { List<ExportInformationExcel> list= informationService.exportBusinessStatis(information); ExcelUtil.export(response, "业务统计数据" + DateUtil.time(), "业务统计数据表", list, ExportInformationExcel.class); } /** * 保安员情况统计导出 */ @GetMapping("/export-security-statis-info") public void exportSecurityStatisInfo(HttpServletResponse response,InformationVO information) { List<ExportInformationSecurityStatistics> list = informationService.exportSecurityStatisInfo(information); ExcelUtil.export(response, "保安员统计数据" + DateUtil.time(), "保安员统计数据表", list, ExportInformationSecurityStatistics.class); } } src/main/java/org/springblade/modules/information/excel/ExportInformationExcel.java
New file @@ -0,0 +1,78 @@ /* * Copyright (c) 2018-2028, Chill Zhuang All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * Redistributions of source code must retain the above copyright notice, * this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in the * documentation and/or other materials provided with the distribution. * Neither the name of the dreamlu.net developer nor the names of its * contributors may be used to endorse or promote products derived from * this software without specific prior written permission. * Author: Chill 庄骞 (smallchill@163.com) */ package org.springblade.modules.information.excel; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.Data; import java.io.Serializable; /** * 业务统计导出 * @author zhongrj * @since 2021-12-14 */ @Data @ColumnWidth(25) @HeadRowHeight(20) @ContentRowHeight(18) public class ExportInformationExcel implements Serializable { private static final long serialVersionUID = 1L; @ColumnWidth(35) @ExcelProperty("企业名称") private String enterpriseName; /** * 公司id(忽略不导出) */ @ExcelIgnore private String departmentid; @ColumnWidth(15) @ExcelProperty("企业类型") private String stats; @ColumnWidth(15) @ExcelProperty("所属辖区") private String jurname; @ColumnWidth(15) @ExcelProperty("保安员人数") private Integer znum; @ColumnWidth(15) @ExcelProperty("持证人数") private Integer cznum; @ColumnWidth(15) @ExcelProperty("派遣人数") private Integer pqnum; @ColumnWidth(15) @ExcelProperty("服务对象数量") private Integer fwnum; @ColumnWidth(15) @ExcelProperty("服务到期数量") private Integer dqnum; } src/main/java/org/springblade/modules/information/excel/ExportInformationSecurityStatistics.java
New file @@ -0,0 +1,94 @@ /* * Copyright (c) 2018-2028, Chill Zhuang All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * Redistributions of source code must retain the above copyright notice, * this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in the * documentation and/or other materials provided with the distribution. * Neither the name of the dreamlu.net developer nor the names of its * contributors may be used to endorse or promote products derived from * this software without specific prior written permission. * Author: Chill 庄骞 (smallchill@163.com) */ package org.springblade.modules.information.excel; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.Data; import java.io.Serializable; /** * 保安员统计 * * @author zhongrj * @since 2021-12-14 */ @Data @ColumnWidth(25) @HeadRowHeight(20) @ContentRowHeight(18) public class ExportInformationSecurityStatistics implements Serializable { private static final long serialVersionUID = 1L; @ColumnWidth(35) @ExcelProperty("企业名称") private String deptname; /** * 公司id(忽略不导出) */ @ExcelIgnore private String departmentid; @ColumnWidth(15) @ExcelProperty("企业类型") private String stats; @ColumnWidth(15) @ExcelProperty("所属辖区") private String jurname; @ColumnWidth(15) @ExcelProperty("保安员人数") private Integer num; @ColumnWidth(15) @ExcelProperty("持证上岗人数") private Integer cznum; @ColumnWidth(15) @ExcelProperty("未派遣人数") private Integer wpqnum; @ColumnWidth(15) @ExcelProperty("派遣人数") private Integer pqnum; @ColumnWidth(15) @ExcelProperty("已采集照片人数") private Integer ycjzpnum; @ColumnWidth(15) @ExcelProperty("未采集照片人数") private Integer wcjzpnum; @ColumnWidth(15) @ExcelProperty("已采集指纹人数") private Integer ycjzwnum; @ColumnWidth(15) @ExcelProperty("资格审查异常人数") private Integer scycnum; @ColumnWidth(15) @ExcelProperty("缴纳社保人数") private Integer jnsbnum; } src/main/java/org/springblade/modules/information/mapper/InformationMapper.java
@@ -20,6 +20,9 @@ import com.baomidou.mybatisplus.core.metadata.IPage; import org.apache.ibatis.annotations.Param; import org.springblade.modules.information.entity.Information; import org.springblade.modules.information.excel.ExportInformationExcel; import org.springblade.modules.information.excel.ExportInformationSecurityStatistics; import org.springblade.modules.information.vo.InformationStatisticsVO; import org.springblade.modules.information.vo.InformationVO; import org.springblade.modules.system.entity.Dept; @@ -98,4 +101,104 @@ * @return */ int selectInformationHoldNumCount(@Param("dept")Dept dept); /** * 业务统计情况 * @param page * @param information * @return */ List<InformationStatisticsVO> selectBusinessStatistics(@Param("page")IPage page,@Param("information") InformationVO information); /** * 查询当前单位是否有子级单位 * @param informationStatisticsVO * @return */ List<Dept> getDeptHashChildrens(@Param("information") InformationStatisticsVO informationStatisticsVO); /** * 服务单位数 * @param dept * @return */ Integer selectInformationUnitNumCount(@Param("dept")Dept dept); /** * 服务单位到期数 * @param dept * @return */ Integer selectInformationUnitExpireNumCount(@Param("dept")Dept dept); /** * 查询单位的未派遣人数 * @param dept * @return */ Integer selectInformationNotDispatcherNumCount(@Param("dept")Dept dept); /** * 查询单位的已采集照片人数 * @param dept * @return */ Integer selectInformationUnitAvatarNumCount(@Param("dept")Dept dept); /** * 查询单位的未采集照片人数 * @param dept * @return */ Integer selectInformationUnitNotAvatarNumCount(@Param("dept")Dept dept); /** * 已采集指纹 * @param dept * @return */ Integer selectInformationUnitZwNumCount(@Param("dept")Dept dept); /** * 审查异常 * @param dept * @return */ Integer selectInformationUnitYcNumCount(@Param("dept")Dept dept); /** * 缴纳社保 * @param dept * @return */ Integer selectInformationUnitJnsbNumCount(@Param("dept")Dept dept); /** * 保安员情况统计 * @param page * @param information * @return */ List<InformationStatisticsVO> selectSecurityStatistics(@Param("page") IPage page, @Param("information") InformationVO information); /** * 查询部门信息 * @param departmentid * @return */ List<Dept> getDeptChildrens(String departmentid); /** * 保安员统计导出 * @param information * @return */ List<ExportInformationSecurityStatistics> exportSecurityStatisInfo(@Param("information") InformationVO information); /** * 业务情况统计导出 * @param information * @return */ List<ExportInformationExcel> exportBusinessStatis(@Param("information") InformationVO information); } src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml
@@ -507,4 +507,738 @@ ) </select> <!--业务统计情况--> <select id="selectBusinessStatistics" resultType="org.springblade.modules.information.vo.InformationStatisticsVO"> SELECT A.enterpriseName, A.departmentid, A.stats, A.jurisdiction, j.dept_name as jurname, IFNULL( C.znum, 0 ) AS znum, IFNULL( E.cznum, 0 ) AS cznum, IFNULL( F.pqnum, 0 ) AS pqnum, IFNULL( B.num, 0 ) AS fwnum, IFNULL( D.dqnum, 0 ) AS dqnum FROM ( SELECT enterpriseName,jurisdiction, departmentid,stats,create_time FROM sys_information WHERE stats!=1 GROUP BY enterpriseName,jurisdiction,departmentid,stats,create_time) A 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, bu.dept_id FROM blade_user bu WHERE bu.`status` = 1 AND bu.is_deleted = 0 AND bu.role_id = '1412226235153731586' GROUP BY bu.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 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 1=1 and status = 1 and is_deleted = 0 and role_id = "1412226235153731586" and dispatch = 0 GROUP BY dept_id ) F ON A.departmentid = F.dept_id LEFT JOIN blade_dept d ON A.departmentid = d.id LEFT JOIN sys_jurisdiction j ON j.id = A.jurisdiction WHERE 1 =1 and d.dept_category = 1 <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'"> and (j.id = #{information.jurisdiction} or j.parent_id = #{information.jurisdiction}) </if> <if test="information.deptid!=null and information.deptid!=''"> and A.enterpriseName like concat('%', #{information.deptid},'%') </if> <if test="information.stats!=null and information.stats!=''"> and A.stats=#{information.stats} </if> <if test="information.startTime!=null and information.startTime!=''"> and A.create_time >= #{information.startTime} </if> <if test="information.endTime!=null and information.endTime!=''"> and A.create_time <= #{information.endTime} </if> </select> <!--查询当前单位是否有子级单位--> <select id="getDeptHashChildrens" resultType="org.springblade.modules.system.entity.Dept"> select * from blade_dept where is_deleted = 0 and parent_id = #{information.departmentid} </select> <!--查询服务单位数--> <select id="selectInformationUnitNumCount" resultType="java.lang.Integer"> SELECT COUNT(*) FROM sys_dispatcher_unit where start_time <= now() and end_time > now() and dept_id in ( SELECT id FROM ( SELECT t1.id,t1.parent_id,t1.dept_name, IF ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild FROM ( SELECT id, parent_id,dept_name FROM blade_dept t ORDER BY parent_id, id ) t1, ( SELECT @pids := #{dept.id} ) t2 ) t3 WHERE ischild != 0 ) </select> <!--查询服务单位到期数--> <select id="selectInformationUnitExpireNumCount" resultType="java.lang.Integer"> SELECT COUNT(*) FROM sys_dispatcher_unit where end_time < now() and dept_id in ( SELECT id FROM ( SELECT t1.id,t1.parent_id,t1.dept_name, IF ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild FROM ( SELECT id, parent_id,dept_name FROM blade_dept t ORDER BY parent_id, id ) t1, ( SELECT @pids := #{dept.id} ) t2 ) t3 WHERE ischild != 0 ) </select> <!--查询单位的未派遣人数--> <select id="selectInformationNotDispatcherNumCount" resultType="java.lang.Integer"> SELECT COUNT( * ) FROM blade_user WHERE 1=1 and role_id = '1412226235153731586' and is_deleted = 0 AND status=1 AND dispatch=1 and dept_id in ( SELECT id FROM ( SELECT t1.id,t1.parent_id,t1.dept_name, IF ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild FROM ( SELECT id, parent_id,dept_name FROM blade_dept t ORDER BY parent_id, id ) t1, ( SELECT @pids := #{dept.id} ) t2 ) t3 WHERE ischild != 0 ) </select> <!--查询单位的已采集照片人数--> <select id="selectInformationUnitAvatarNumCount" resultType="java.lang.Integer"> SELECT COUNT(*) FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND avatar is not null and avatar!="" and dept_id in ( SELECT id FROM ( SELECT t1.id,t1.parent_id,t1.dept_name, IF ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild FROM ( SELECT id, parent_id,dept_name FROM blade_dept t ORDER BY parent_id, id ) t1, ( SELECT @pids := #{dept.id} ) t2 ) t3 WHERE ischild != 0 ) </select> <!--查询单位的未采集照片人数--> <select id="selectInformationUnitNotAvatarNumCount" resultType="java.lang.Integer"> SELECT COUNT(*) FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND (avatar is null or avatar = "") and dept_id in ( SELECT id FROM ( SELECT t1.id,t1.parent_id,t1.dept_name, IF ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild FROM ( SELECT id, parent_id,dept_name FROM blade_dept t ORDER BY parent_id, id ) t1, ( SELECT @pids := #{dept.id} ) t2 ) t3 WHERE ischild != 0 ) </select> <!--查询单位的已采集指纹人数--> <select id="selectInformationUnitZwNumCount" resultType="java.lang.Integer"> SELECT COUNT(*) FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND fingerprint is not null and fingerprint!="" and dept_id in ( SELECT id FROM ( SELECT t1.id,t1.parent_id,t1.dept_name, IF ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild FROM ( SELECT id, parent_id,dept_name FROM blade_dept t ORDER BY parent_id, id ) t1, ( SELECT @pids := #{dept.id} ) t2 ) t3 WHERE ischild != 0 ) </select> <!--查询单位的审查异常人数--> <select id="selectInformationUnitYcNumCount" resultType="java.lang.Integer"> SELECT COUNT(*) FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND examination_type = 1 and dept_id in ( SELECT id FROM ( SELECT t1.id,t1.parent_id,t1.dept_name, IF ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild FROM ( SELECT id, parent_id,dept_name FROM blade_dept t ORDER BY parent_id, id ) t1, ( SELECT @pids := #{dept.id} ) t2 ) t3 WHERE ischild != 0 ) </select> <!--查询单位的社保缴纳人数--> <select id="selectInformationUnitJnsbNumCount" resultType="java.lang.Integer"> SELECT COUNT(*) FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND soil = 0 and dept_id in ( SELECT id FROM ( SELECT t1.id,t1.parent_id,t1.dept_name, IF ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild FROM ( SELECT id, parent_id,dept_name FROM blade_dept t ORDER BY parent_id, id ) t1, ( SELECT @pids := #{dept.id} ) t2 ) t3 WHERE ischild != 0 ) </select> <!--保安员统计查询--> <select id="selectSecurityStatistics" resultType="org.springblade.modules.information.vo.InformationStatisticsVO"> SELECT IFNULL( V.num, 0 ) AS num, IFNULL( B.cznum, 0 ) AS cznum, IFNULL( D.num, 0 ) AS wpqnum, IFNULL( C.num, 0 ) AS pqnum, IFNULL( H.ycaijzpnum, 0 ) AS ycjzpnum, IFNULL( F.wcaijnum, 0 ) AS wcjzpnum, IFNULL( I.ycaijzwnum, 0 ) AS ycjzwnum, IFNULL( G.yicnum, 0 ) AS scycnum, IFNULL( E.sbnum, 0 ) AS jnsbnum, A.departmentid, A.enterpriseName AS deptname, A.stats, j.dept_name AS jurname FROM ( SELECT jurisdiction, departmentid, enterpriseName, stats, create_time FROM sys_information WHERE stats != 1 ) A LEFT JOIN ( SELECT COUNT( * ) AS num, bu.dept_id FROM blade_user bu WHERE bu.`status` = 1 AND bu.is_deleted = 0 AND bu.role_id = '1412226235153731586' GROUP BY bu.dept_id ) V ON V.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS cznum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND hold = 1 GROUP BY dept_id ) B ON A.departmentid = B.dept_id LEFT JOIN ( SELECT COUNT( * ) AS num, bu.dept_id FROM blade_user bu WHERE bu.`status` = 1 AND bu.is_deleted = 0 AND bu.role_id = '1412226235153731586' AND bu.dispatch = 0 GROUP BY bu.dept_id ) C ON C.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS num, bu.dept_id FROM blade_user bu WHERE bu.`status` = 1 AND bu.is_deleted = 0 AND bu.role_id = '1412226235153731586' AND bu.dispatch = 1 GROUP BY bu.dept_id ) D ON D.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS sbnum,dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND soil = 0 GROUP BY dept_id ) E ON E.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS wcaijnum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND (avatar is null or avatar = "") GROUP BY dept_id ) F ON F.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS ycaijzpnum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND avatar is not null and avatar!="" GROUP BY dept_id ) H ON H.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS ycaijzwnum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND fingerprint is not null and fingerprint!="" GROUP BY dept_id ) I ON I.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS yicnum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND examination_type = 1 GROUP BY dept_id ) G ON G.dept_id = A.departmentid LEFT JOIN blade_dept d ON A.departmentid = d.id LEFT JOIN sys_jurisdiction j ON j.id = A.jurisdiction WHERE 1 = 1 AND d.dept_category = 1 <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'"> and (j.id = #{information.jurisdiction} or j.parent_id = #{information.jurisdiction}) </if> <if test="information.deptid!=null and information.deptid!=''"> and A.enterpriseName like concat('%', #{information.deptid},'%') </if> <if test="information.stats!=null and information.stats!=''"> and A.stats=#{information.stats} </if> <if test="information.startTime!=null and information.startTime!=''"> and A.create_time >= #{information.startTime} </if> <if test="information.endTime!=null and information.endTime!=''"> and A.create_time <= #{information.endTime} </if> </select> <!--查询当前单位是否有子级单位--> <select id="getDeptChildrens" resultType="org.springblade.modules.system.entity.Dept"> select * from blade_dept where is_deleted = 0 and parent_id = #{param1} </select> <!--导出保安员统计查询--> <select id="exportSecurityStatisInfo" resultType="org.springblade.modules.information.excel.ExportInformationSecurityStatistics"> SELECT IFNULL( V.num, 0 ) AS num, IFNULL( B.cznum, 0 ) AS cznum, IFNULL( D.num, 0 ) AS wpqnum, IFNULL( C.num, 0 ) AS pqnum, IFNULL( H.ycaijzpnum, 0 ) AS ycjzpnum, IFNULL( F.wcaijnum, 0 ) AS wcjzpnum, IFNULL( I.ycaijzwnum, 0 ) AS ycjzwnum, IFNULL( G.yicnum, 0 ) AS scycnum, IFNULL( E.sbnum, 0 ) AS jnsbnum, A.departmentid, A.enterpriseName AS deptname, case when A.stats=0 then '自招保安单位' when A.stats=2 then '本市保安公司' when A.stats=4 then '分公司' else "其他" end as stats, j.dept_name AS jurname FROM ( SELECT jurisdiction, departmentid, enterpriseName, stats, create_time FROM sys_information WHERE stats != 1 ) A LEFT JOIN ( SELECT COUNT( * ) AS num, bu.dept_id FROM blade_user bu WHERE bu.`status` = 1 AND bu.is_deleted = 0 AND bu.role_id = '1412226235153731586' GROUP BY bu.dept_id ) V ON V.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS cznum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND hold = 1 GROUP BY dept_id ) B ON A.departmentid = B.dept_id LEFT JOIN ( SELECT COUNT( * ) AS num, bu.dept_id FROM blade_user bu WHERE bu.`status` = 1 AND bu.is_deleted = 0 AND bu.role_id = '1412226235153731586' AND bu.dispatch = 0 GROUP BY bu.dept_id ) C ON C.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS num, bu.dept_id FROM blade_user bu WHERE bu.`status` = 1 AND bu.is_deleted = 0 AND bu.role_id = '1412226235153731586' AND bu.dispatch = 1 GROUP BY bu.dept_id ) D ON D.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS sbnum,dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND soil = 0 GROUP BY dept_id ) E ON E.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS wcaijnum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND (avatar is null or avatar = "") GROUP BY dept_id ) F ON F.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS ycaijzpnum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND avatar is not null and avatar!="" GROUP BY dept_id ) H ON H.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS ycaijzwnum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND fingerprint is not null and fingerprint!="" GROUP BY dept_id ) I ON I.dept_id = A.departmentid LEFT JOIN ( SELECT COUNT( * ) AS yicnum, dept_id FROM blade_user WHERE role_id = '1412226235153731586' AND STATUS = 1 AND is_deleted = 0 AND examination_type = 1 GROUP BY dept_id ) G ON G.dept_id = A.departmentid LEFT JOIN blade_dept d ON A.departmentid = d.id LEFT JOIN sys_jurisdiction j ON j.id = A.jurisdiction WHERE 1 = 1 AND d.dept_category = 1 <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'"> and (j.id = #{information.jurisdiction} or j.parent_id = #{information.jurisdiction}) </if> <if test="information.deptid!=null and information.deptid!=''"> and A.enterpriseName like concat('%', #{information.deptid},'%') </if> <if test="information.stats!=null and information.stats!=''"> and A.stats=#{information.stats} </if> <if test="information.startTime!=null and information.startTime!=''"> and A.create_time >= #{information.startTime} </if> <if test="information.endTime!=null and information.endTime!=''"> and A.create_time <= #{information.endTime} </if> </select> <!--导出业务统计情况--> <select id="exportBusinessStatis" resultType="org.springblade.modules.information.excel.ExportInformationExcel"> SELECT A.enterpriseName, A.departmentid, case when A.stats=0 then '自招保安单位' when A.stats=2 then '本市保安公司' when A.stats=4 then '分公司' else "其他" end as stats, A.jurisdiction, j.dept_name as jurname, IFNULL( C.znum, 0 ) AS znum, IFNULL( E.cznum, 0 ) AS cznum, IFNULL( F.pqnum, 0 ) AS pqnum, IFNULL( B.num, 0 ) AS fwnum, IFNULL( D.dqnum, 0 ) AS dqnum FROM ( SELECT enterpriseName,jurisdiction, departmentid,stats,create_time FROM sys_information WHERE stats!=1 GROUP BY enterpriseName,jurisdiction,departmentid,stats,create_time) A 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, bu.dept_id FROM blade_user bu WHERE bu.`status` = 1 AND bu.is_deleted = 0 AND bu.role_id = '1412226235153731586' GROUP BY bu.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 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 1=1 and status = 1 and is_deleted = 0 and role_id = "1412226235153731586" and dispatch = 0 GROUP BY dept_id ) F ON A.departmentid = F.dept_id LEFT JOIN blade_dept d ON A.departmentid = d.id LEFT JOIN sys_jurisdiction j ON j.id = A.jurisdiction WHERE 1 =1 and d.dept_category = 1 <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'"> and (j.id = #{information.jurisdiction} or j.parent_id = #{information.jurisdiction}) </if> <if test="information.deptid!=null and information.deptid!=''"> and A.enterpriseName like concat('%', #{information.deptid},'%') </if> <if test="information.stats!=null and information.stats!=''"> and A.stats=#{information.stats} </if> <if test="information.startTime!=null and information.startTime!=''"> and A.create_time >= #{information.startTime} </if> <if test="information.endTime!=null and information.endTime!=''"> and A.create_time <= #{information.endTime} </if> </select> </mapper> src/main/java/org/springblade/modules/information/service/IInformationService.java
@@ -19,6 +19,8 @@ import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.IService; import org.springblade.modules.information.entity.Information; import org.springblade.modules.information.excel.ExportInformationExcel; import org.springblade.modules.information.excel.ExportInformationSecurityStatistics; import org.springblade.modules.information.excel.InforExcel; import org.springblade.modules.information.vo.InformationVO; import org.springblade.modules.system.entity.User; @@ -80,4 +82,34 @@ */ void deleteUserByDeptId(String deptId); String selJurchilder(String id); /** * 业务统计情况 * @param page * @param information * @return */ IPage selectBusinessStatistics(IPage<List> page, InformationVO information); /** * 保安员情况统计 * @param page * @param information * @return */ IPage selectSecurityStatistics(IPage<List> page, InformationVO information); /** * 业务统计导出 * @param information * @return */ List<ExportInformationExcel> exportBusinessStatis(InformationVO information); /** * 保安员统计导出 * @param information * @return */ List<ExportInformationSecurityStatistics> exportSecurityStatisInfo(InformationVO information); } src/main/java/org/springblade/modules/information/service/impl/InformationServiceImpl.java
@@ -32,9 +32,12 @@ import org.springblade.core.tool.utils.Func; import org.springblade.core.tool.utils.StringUtil; import org.springblade.modules.information.entity.Information; import org.springblade.modules.information.excel.ExportInformationExcel; import org.springblade.modules.information.excel.ExportInformationSecurityStatistics; import org.springblade.modules.information.excel.InforExcel; import org.springblade.modules.information.mapper.InformationMapper; import org.springblade.modules.information.service.IInformationService; import org.springblade.modules.information.vo.InformationStatisticsVO; import org.springblade.modules.information.vo.InformationVO; import org.springblade.modules.system.entity.Dept; import org.springblade.modules.system.entity.Role; @@ -213,4 +216,178 @@ public String selJurchilder(String id) { return baseMapper.selJurchilder(id); } /** * 业务统计情况 * @param page * @param information * @return */ @Override public IPage selectBusinessStatistics(IPage page, InformationVO information) { List<InformationStatisticsVO> list = baseMapper.selectBusinessStatistics(page, information); if (list.size()>0) { //遍历 for (InformationStatisticsVO informationStatisticsVO : list) { List<Dept> deptList = baseMapper.getDeptHashChildrens(informationStatisticsVO); //有子级 if (deptList.size()>0){ Dept dept = new Dept(); dept.setId(Long.parseLong(informationStatisticsVO.getDepartmentid())); //查询当前子单位的人数 Integer z = baseMapper.selectInformationUserNumCount(dept) + informationStatisticsVO.getZnum(); informationStatisticsVO.setZnum(z); //已派遣人数 Integer p = baseMapper.selectInformationDispatcherNumCount(dept) + informationStatisticsVO.getPqnum(); informationStatisticsVO.setPqnum(p); //持证人数 Integer c = baseMapper.selectInformationHoldNumCount(dept) + informationStatisticsVO.getCznum(); informationStatisticsVO.setCznum(c); //服务单位数 Integer f = baseMapper.selectInformationUnitNumCount(dept) + informationStatisticsVO.getFwnum(); informationStatisticsVO.setFwnum(f); //服务单位到期数 Integer d = baseMapper.selectInformationUnitExpireNumCount(dept) + informationStatisticsVO.getDqnum(); informationStatisticsVO.setDqnum(d); } } } return page.setRecords(list); } /** * 保安员情况统计 * @param page * @param information * @return */ @Override public IPage selectSecurityStatistics(IPage page, InformationVO information) { List<InformationStatisticsVO> list = baseMapper.selectSecurityStatistics(page,information); if (list.size()>0) { //遍历 for (InformationStatisticsVO informationStatisticsVO : list) { List<Dept> deptList = baseMapper.getDeptHashChildrens(informationStatisticsVO); //有子级 if (deptList.size()>0){ Dept dept = new Dept(); dept.setId(Long.parseLong(informationStatisticsVO.getDepartmentid())); //查询当前子单位的人数 Integer z = baseMapper.selectInformationUserNumCount(dept) + informationStatisticsVO.getNum(); informationStatisticsVO.setNum(z); //持证人数 Integer c = baseMapper.selectInformationHoldNumCount(dept) + informationStatisticsVO.getCznum(); informationStatisticsVO.setCznum(c); //已派遣人数 Integer p = baseMapper.selectInformationDispatcherNumCount(dept) + informationStatisticsVO.getPqnum(); informationStatisticsVO.setPqnum(p); //未派遣人数 Integer wpq = baseMapper.selectInformationNotDispatcherNumCount(dept) + informationStatisticsVO.getWpqnum(); informationStatisticsVO.setWpqnum(wpq); //已采集照片数 Integer ycjzp = baseMapper.selectInformationUnitAvatarNumCount(dept) + informationStatisticsVO.getYcjzpnum(); informationStatisticsVO.setYcjzpnum(ycjzp); //未采集照片数 Integer wcjzp = baseMapper.selectInformationUnitNotAvatarNumCount(dept) + informationStatisticsVO.getWcjzpnum(); informationStatisticsVO.setWcjzpnum(wcjzp); //已采集指纹数 Integer ycjzw = baseMapper.selectInformationUnitZwNumCount(dept) + informationStatisticsVO.getYcjzwnum(); informationStatisticsVO.setYcjzwnum(ycjzw); //审查异常数 Integer scyc = baseMapper.selectInformationUnitYcNumCount(dept) + informationStatisticsVO.getScycnum(); informationStatisticsVO.setScycnum(scyc); //缴纳社保数 Integer jnsb = baseMapper.selectInformationUnitJnsbNumCount(dept) + informationStatisticsVO.getJnsbnum(); informationStatisticsVO.setJnsbnum(jnsb); } } } return page.setRecords(list); } /** * 业务统计导出 * @param information * @return */ @Override public List<ExportInformationExcel> exportBusinessStatis(InformationVO information) { List<ExportInformationExcel> list = baseMapper.exportBusinessStatis(information); if (list.size()>0) { //遍历 for (ExportInformationExcel informationStatisticsVO : list) { List<Dept> deptList = baseMapper.getDeptChildrens(informationStatisticsVO.getDepartmentid()); //有子级 if (deptList.size()>0){ Dept dept = new Dept(); dept.setId(Long.parseLong(informationStatisticsVO.getDepartmentid())); //查询当前子单位的人数 Integer z = baseMapper.selectInformationUserNumCount(dept) + informationStatisticsVO.getZnum(); informationStatisticsVO.setZnum(z); //已派遣人数 Integer p = baseMapper.selectInformationDispatcherNumCount(dept) + informationStatisticsVO.getPqnum(); informationStatisticsVO.setPqnum(p); //持证人数 Integer c = baseMapper.selectInformationHoldNumCount(dept) + informationStatisticsVO.getCznum(); informationStatisticsVO.setCznum(c); //服务单位数 Integer f = baseMapper.selectInformationUnitNumCount(dept) + informationStatisticsVO.getFwnum(); informationStatisticsVO.setFwnum(f); //服务单位到期数 Integer d = baseMapper.selectInformationUnitExpireNumCount(dept) + informationStatisticsVO.getDqnum(); informationStatisticsVO.setDqnum(d); } } } return list; } /** * 保安员统计导出 * @param information * @return */ @Override public List<ExportInformationSecurityStatistics> exportSecurityStatisInfo(InformationVO information) { List<ExportInformationSecurityStatistics> list = baseMapper.exportSecurityStatisInfo(information); if (list.size()>0) { //遍历 for (ExportInformationSecurityStatistics informationStatisticsVO : list) { List<Dept> deptList = baseMapper.getDeptChildrens(informationStatisticsVO.getDepartmentid()); //有子级 if (deptList.size()>0){ Dept dept = new Dept(); dept.setId(Long.parseLong(informationStatisticsVO.getDepartmentid())); //查询当前子单位的人数 Integer z = baseMapper.selectInformationUserNumCount(dept) + informationStatisticsVO.getNum(); informationStatisticsVO.setNum(z); //持证人数 Integer c = baseMapper.selectInformationHoldNumCount(dept) + informationStatisticsVO.getCznum(); informationStatisticsVO.setCznum(c); //已派遣人数 Integer p = baseMapper.selectInformationDispatcherNumCount(dept) + informationStatisticsVO.getPqnum(); informationStatisticsVO.setPqnum(p); //未派遣人数 Integer wpq = baseMapper.selectInformationNotDispatcherNumCount(dept) + informationStatisticsVO.getWpqnum(); informationStatisticsVO.setWpqnum(wpq); //已采集照片数 Integer ycjzp = baseMapper.selectInformationUnitAvatarNumCount(dept) + informationStatisticsVO.getYcjzpnum(); informationStatisticsVO.setYcjzpnum(ycjzp); //未采集照片数 Integer wcjzp = baseMapper.selectInformationUnitNotAvatarNumCount(dept) + informationStatisticsVO.getWcjzpnum(); informationStatisticsVO.setWcjzpnum(wcjzp); //已采集指纹数 Integer ycjzw = baseMapper.selectInformationUnitZwNumCount(dept) + informationStatisticsVO.getYcjzwnum(); informationStatisticsVO.setYcjzwnum(ycjzw); //审查异常数 Integer scyc = baseMapper.selectInformationUnitYcNumCount(dept) + informationStatisticsVO.getScycnum(); informationStatisticsVO.setScycnum(scyc); //缴纳社保数 Integer jnsb = baseMapper.selectInformationUnitJnsbNumCount(dept) + informationStatisticsVO.getJnsbnum(); informationStatisticsVO.setJnsbnum(jnsb); } } } return list; } } src/main/java/org/springblade/modules/information/vo/InformationStatisticsVO.java
New file @@ -0,0 +1,115 @@ /* * Copyright (c) 2018-2028, Chill Zhuang All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * Redistributions of source code must retain the above copyright notice, * this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in the * documentation and/or other materials provided with the distribution. * Neither the name of the dreamlu.net developer nor the names of its * contributors may be used to endorse or promote products derived from * this software without specific prior written permission. * Author: Chill 庄骞 (smallchill@163.com) */ package org.springblade.modules.information.vo; import lombok.Data; /** * 视图实体类 * * @author BladeX * @since 2021-12-02 */ @Data public class InformationStatisticsVO { private static final long serialVersionUID = 1L; /** * 公司名称 */ private String enterpriseName; /** * 公司名称 */ private String deptname; /** * 公司id */ private String departmentid; /** * 公司类型 */ private String stats; /** * 辖区名称 */ private String jurname; /** * 保安员人数 */ private Integer num; /** * 总人数 */ private Integer znum; /** * 持证人数 */ private Integer cznum; /** * 派遣人数 */ private Integer pqnum; /** * 服务单位数 */ private Integer fwnum; /** * 到期单位数 */ private Integer dqnum; /** * 未派遣 */ private Integer wpqnum; /** * 已采集照片 */ private Integer ycjzpnum; /** * 未采集照片 */ private Integer wcjzpnum; /** * 已采集指纹 */ private Integer ycjzwnum; /** * 审查异常 */ private Integer scycnum; /** * 缴纳社保 */ private Integer jnsbnum; } src/main/java/org/springblade/modules/information/vo/InformationVO.java
@@ -47,6 +47,21 @@ */ private String createDeptId; /** * 开始时间 */ private String startTime; /** * 结束时间 */ private String endTime; /** * 公司名稱 */ private String deptid; }
