分局预警接口修改

Administrator

2021-09-18 6ad9c778d138d0806966ce044dd4ee2ee46d2ea2

分局预警接口修改

 src/main/java/org/springblade/modules/information/controller/InformationsController.java

@@ -149,6 +149,32 @@
        //辖区信息
        List<Map<Object, Object>> list = informationService.selectJur();
        List<Map<String, Object>> lists = new ArrayList<>();

        //1.保安员表现差预警数量
        List<ResponseVo> performanceGroupCountList = informationService.getSecurityPerformanceGroupCountList();
        System.out.println("保安员表现差预警数量 = " + performanceGroupCountList);

        //2.保安员资格异常的数量
        List<ResponseVo> examinationGroupCountList = informationService.getSecurityExaminationGroupCountList();
        System.out.println("保安员资格异常的数量 = " + examinationGroupCountList);

        //3.考试通过率低于50%
        List<ResponseVo> examPassingGroupCountList = informationService.getSecurityExamPassingGroupCountList();
        System.out.println("考试通过率低于50% = " + examPassingGroupCountList);

        //4.持证率低于50%
        List<ResponseVo> holdGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(1);
        System.out.println("持证率低于50% = " + holdGroupCountList);

        //5.社保缴纳率低于50%
        List<ResponseVo> soidGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(2);
        System.out.println("社保缴纳率低于50% = " + soidGroupCountList);

        //6.派遣率低于50%
        List<ResponseVo> dispatchGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(3);
        System.out.println("派遣率低于50% = " + dispatchGroupCountList);


        for (int i = 0; i < list.size(); i++) {
            int count = 0;
            Map<String, Object> map = new HashMap<String, Object>();
@@ -156,9 +182,8 @@
            String id = list.get(i).get("id").toString();
            //辖区名称
            String jurname = list.get(i).get("dept_name").toString();

            //1.保安员表现差预警数量
            List<ResponseVo> performanceGroupCountList = informationService.getSecurityPerformanceGroupCountList();
            System.out.println("保安员表现差预警数量 = " + performanceGroupCountList);
            //遍历计算总数
            for (ResponseVo responseVo : performanceGroupCountList) {
                //辖区id相同则数量相加
@@ -169,8 +194,6 @@


            //2.保安员资格异常的数量
            List<ResponseVo> examinationGroupCountList = informationService.getSecurityExaminationGroupCountList();
            System.out.println("保安员资格异常的数量 = " + examinationGroupCountList);
            //遍历计算总数
            for (ResponseVo responseVo : examinationGroupCountList) {
                //辖区id相同则数量相加
@@ -180,8 +203,6 @@
            }

            //3.考试通过率低于50%
            List<ResponseVo> examPassingGroupCountList = informationService.getSecurityExamPassingGroupCountList();
            System.out.println("考试通过率低于50% = " + examPassingGroupCountList);
            //遍历计算总数
            for (ResponseVo responseVo : examPassingGroupCountList) {
                //辖区id相同则数量相加
@@ -191,8 +212,6 @@
            }

            //4.持证率低于50%
            List<ResponseVo> holdGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(1);
            System.out.println("持证率低于50% = " + holdGroupCountList);
            //遍历计算总数
            for (ResponseVo responseVo : holdGroupCountList) {
                //辖区id相同则数量相加
@@ -202,8 +221,6 @@
            }

            //5.社保缴纳率低于50%
            List<ResponseVo> soidGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(2);
            System.out.println("社保缴纳率低于50% = " + soidGroupCountList);
            //遍历计算总数
            for (ResponseVo responseVo : soidGroupCountList) {
                //辖区id相同则数量相加
@@ -213,8 +230,6 @@
            }

            //6.派遣率低于50%
            List<ResponseVo> dispatchGroupCountList = informationService.getSecurityHoldAndSoidAndDispatchGroupCountList(3);
            System.out.println("派遣率低于50% = " + dispatchGroupCountList);
            //遍历计算总数
            for (ResponseVo responseVo : dispatchGroupCountList) {
                //辖区id相同则数量相加

 src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml

@@ -2017,23 +2017,36 @@

    <!--根据辖区查询统计保安公司(1:持证率低于50% 2:派遣率低于50%  3：缴纳社保率低于50% 的保安公司个数及保安公司分页信息（本市保安公司，分公司）-->
    <select id="getSecurityUnitLess50Page" resultType="org.springblade.modules.information.vo.InformationVO">
        select c.*,sj.dept_name jurisdictionName from (
        select c.*,sj.dept_name jurisdictionName,si.enterpriseName enterprisename from (
        select
        si.*,
        a.dept_id,
        ifnull(a.count,0) znum,
        ifnull(b.count,0) cnum,
        ifnull(FORMAT((b.count/a.count)*100,2),0) pnum
        from sys_information si
        left join
        from
        (
        <include refid="getSecurity"/>
        select dept_id,
        ifnull(count(*), 0) count
        from blade_user bu
        left join blade_role br
        on bu.role_id = br.id
        where
        role_alias = "安保人员"
        and bu.is_deleted = 0
        and bu.`status` = 1
        group by dept_id
        ) a
        on
        a.dept_id = si.departmentid
        left join
        (
        <include refid="getSecurity"/>
        select dept_id,
        ifnull(count(*), 0) count
        from blade_user bu
        left join blade_role br
        on bu.role_id = br.id
        where
        role_alias = "安保人员"
        and bu.is_deleted = 0
        and bu.`status` = 1
        <if test="type==1">
            and bu.hold = 1
        </if>
@@ -2049,14 +2062,18 @@
        group by dept_id
        ) b
        on
        b.dept_id = si.departmentid
        a.dept_id = b.dept_id
        )c
        left join
        sys_information si
        on
        si.departmentid = c.dept_id
        left join
        sys_jurisdiction sj
        on
        c.jurisdiction = sj.id
        si.jurisdiction = sj.id
        where 1=1
        and (c.stats = 2 or c.stats = 4)
        and (si.stats = 2 or si.stats = 4)
        and pnum &lt; #{information.proportion}
        <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
            and (sj.id = #{information.jurisdiction} or sj.parent_id = #{information.jurisdiction})
@@ -2179,82 +2196,344 @@

    <!--根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司）预警信息，包含（未持证，现实表现差，未缴纳社保，资格审查异常）-->
    <select id="getSubstationWarnInfoPage" resultType="org.springblade.modules.information.vo.InformationVO">
        select jurisdictionName,enterprisename,detail from (
        (
        select
        si.jurisdiction id,
        sj.parent_id,
        sj.dept_name jurisdictionName,
        si.enterprisename,
        "表现差" detail
        from sys_performance sp
        left join
        blade_user bu
        on
        bu.id = sp.securityId
        left join
        sys_information si
        on
        bu.dept_id = si.departmentid
        left join
        sys_jurisdiction sj
        on
        sj.id = si.jurisdiction
        where
        1=1
        and (si.stats = 2 or si.stats =4)
        and bu.is_deleted = 0
        and bu.`status` = 1
        and sp.score = 3
        )

        union
        (
        select
        sj.id,
        sj.parent_id,
        sj.dept_name jurisdictionName,
        si.enterprisename,
        if(bu.hold=2,"未持证","证件吊销") as detail
        from
        blade_user bu
        <include refid="getSubstationWarn"/>
        and (bu.hold = 2 or bu.hold = 3)
        )


        union
        (
        select
        sj.id,
        sj.parent_id,
        sj.dept_name jurisdictionName,
        si.enterprisename,
        "未缴纳社保" as detail
        from
        blade_user bu
        <include refid="getSubstationWarn"/>
        and bu.soil = 1
        )

        union
        (
        select
        sj.id,
        sj.parent_id,
        sj.dept_name jurisdictionName,
        si.enterprisename,
        "审查异常" as detail
        from
        blade_user bu
        <include refid="getSubstationWarn"/>
        and bu.examination_type = 1
        )
        (select
            sj1.dept_name jurisdictionName,
            si1.enterpriseName enterprisename,
            "表现差" detail,
            a.count znum
        from (
            select
            bu.dept_id,count(*) count
            from sys_performance sp
            left join
            blade_user bu
            on
            bu.id = sp.securityId
            where
            1=1
            and bu.is_deleted = 0
            and bu.`status` = 1
            and sp.score = 3
            GROUP BY bu.dept_id
        ) a
        left join
        sys_information si1
        on
        a.dept_id = si1.departmentid
        left join
        sys_jurisdiction sj1
        on
        sj1.id = si1.jurisdiction
        where 1=1
        and (si1.stats = 2 or si1.stats =4)
        <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
            and (a.id = #{information.jurisdiction} or a.parent_id = #{information.jurisdiction})
            and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
        </if>
        )

        union

        (
            select
            sj1.dept_name jurisdictionName,
            si1.enterpriseName enterprisename,
            "资格审查异常" detail,
            a.count znum
            from (
                select
                bu.dept_id,count(*) count
                from
                blade_user bu
                where
                1=1
                and bu.is_deleted = 0
                and bu.`status` = 1
                and bu.examination_type = 1
                GROUP BY bu.dept_id
            ) a
            left join
            sys_information si1
            on
            a.dept_id = si1.departmentid
            left join
            sys_jurisdiction sj1
            on
            sj1.id = si1.jurisdiction
            where 1=1
            and (si1.stats = 2 or si1.stats =4)
            <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
                and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
            </if>
        )

        union

        (
        select
        sj1.dept_name jurisdictionName,
        si1.enterpriseName enterprisename,
        "考试通过率低于50%" detail,
        e.count znum
        from
        (
            select d.dept_id,count(*) count from (
                select a.dept_id, ifnull(b.count, 0) num1, ifnull(c.count, 0) num2,ifnull(FORMAT((c.count/b.count)*100, 2), 0) num3 from
                    (
                    select
                    bu.dept_id, ifnull(count(*), 0) count
                    from blade_user bu
                    left join blade_role br
                    on bu.role_id = br.id
                    where
                    role_alias = "安保人员"
                    and bu.is_deleted = 0
                    and bu.`status` = 1
                    group by dept_id
                    ) a
                    left join
                    (
                    select count(*) count, bu.dept_id from
                    exam_score es
                    left join
                    blade_user bu
                    on
                    bu.id = es.user_id
                    where 1=1 group by bu.dept_id
                    ) b
                    on
                    a.dept_id = b.dept_id
                    left join
                    (
                    select count(*) count, bu.dept_id from
                    exam_score es
                    left join
                    blade_user bu
                    on
                    bu.id = es.user_id
                    where qualified = 0 group by bu.dept_id
                    ) c
                on
                c.dept_id = a.dept_id
                where 1=1
            ) d
            where 1=1
            and num1!=0
            and num3 &lt; 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 &lt; 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 &lt; 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 &lt; 50
            GROUP BY c.dept_id
            ) e
            left join
            sys_information si1
            on
            e.dept_id = si1.departmentid
            left join
            sys_jurisdiction sj1
            on
            sj1.id = si1.jurisdiction
            where 1=1
            AND ( stats = 2 OR stats = 4 )
            <if test="information.jurisdiction!=null and information.jurisdiction!='' and information.jurisdiction!='1372091709474910209'">
                and (sj1.id = #{information.jurisdiction} or sj1.parent_id = #{information.jurisdiction})
            </if>
        )

    </select>

    <!--根据辖区查询统计当前辖区下的保安公司（本市保安公司，分公司））服务对象小于2家的公司数量的分页信息-->
@@ -2305,7 +2584,7 @@
    <!--统计表现差的信息-->
    <select id="getSecurityPerformanceGroupCountList" resultType="org.springblade.modules.information.vo.ResponseVo">
        select a.id,
               a.dept_name        name,
               a.dept_name name,
               ifnull(b.count, 0) num
        from (select id, dept_name from sys_jurisdiction where parent_id = 1372091709474910209) a
                 left join
@@ -2336,7 +2615,7 @@
                 group by si.jurisdiction
             ) b
             on
                 a.id = b.parent_id
            a.id = b.parent_id
    </select>


@@ -2510,57 +2789,57 @@
        (select id,dept_name from sys_jurisdiction where parent_id = 1372091709474910209) g
        left join
        (
        select c.id,c.parent_id,count(*) count from (
        select si.enterpriseName,sj.id,sj.parent_id,a.dept_id,ifnull(a.count,0) num1,ifnull(b.count,0) num2,ifnull(FORMAT((b.count/a.count)*100,2),0) num3 from sys_information si
        left join
        (
        select
        bu.dept_id,ifnull(count(*),0) count
        from blade_user bu
        left join blade_role br
        on bu.role_id = br.id
        where
        role_alias = "安保人员"
        and bu.is_deleted = 0
        and bu.`status` = 1
        group by dept_id
        ) a
        on
        si.departmentid = a.dept_id

        left join
        (
        select
        bu.dept_id,ifnull(count(*),0) count
        from blade_user bu
        left join blade_role br
        on bu.role_id = br.id
        where
        role_alias = "安保人员"
        and bu.is_deleted = 0
        and bu.`status` = 1
        <if test="type==1">
            and bu.hold = 1
        </if>
        <if test="type==2">
            and bu.dispatch = 0
        </if>
        <if test="type==3">
            and bu.soil = 0
        </if>
        group by dept_id
        ) b
        on
        si.departmentid = b.dept_id
        left join
        sys_jurisdiction sj
        on
        sj.id = si.jurisdiction
        where 1=1
        and (stats = 2 or stats=4)
        )c
        where c.num3 &lt; 50
        group by c.id
            select c.id,c.parent_id,count(*) count from (
                select si.enterpriseName,sj.id,sj.parent_id,a.dept_id,ifnull(a.count,0) num1,ifnull(b.count,0) num2,ifnull(FORMAT((b.count/a.count)*100,2),0) num3 from
                (
                select
                bu.dept_id,ifnull(count(*),0) count
                from blade_user bu
                left join blade_role br
                on bu.role_id = br.id
                where
                role_alias = "安保人员"
                and bu.is_deleted = 0
                and bu.`status` = 1
                group by dept_id
                ) a
                left join
                (
                select
                bu.dept_id,ifnull(count(*),0) count
                from blade_user bu
                left join blade_role br
                on bu.role_id = br.id
                where
                role_alias = "安保人员"
                and bu.is_deleted = 0
                and bu.`status` = 1
                <if test="type==1">
                    and bu.hold = 1
                </if>
                <if test="type==2">
                    and bu.dispatch = 0
                </if>
                <if test="type==3">
                    and bu.soil = 0
                </if>
                group by dept_id
                ) b
                on
                a.dept_id = b.dept_id
                left join
                sys_information si
                on
                a.dept_id = si.departmentid
                left join
                sys_jurisdiction sj
                on
                sj.id = si.jurisdiction
                where 1=1
                and (stats = 2 or stats=4)
            )c
            where c.num3 &lt; 50
            group by c.id
        ) f
        on
        g.id = f.parent_id

 src/main/java/org/springblade/modules/system/mapper/UserMapper.xml

@@ -157,7 +157,11 @@
        sys_information si
        on
        si.departmentid = bu.dept_id
        where is_deleted = 0
        left join
        sys_jurisdiction sj
        on
        sj.id =si.jurisdiction
        where bu.is_deleted = 0
        and status = 1
        <if test="user.realName!=null and user.realName != ''">
            and bu.real_name like concat(concat('%', #{user.realName}),'%')
@@ -181,7 +185,7 @@
            and bu.hold = #{user.hold}
        </if>
        <if test="user.jurisdiction!=null and user.jurisdiction != '' and user.jurisdiction != '1372091709474910209'">
            and si.jurisdiction = #{user.jurisdiction}
            and sj.parent_id = #{user.jurisdiction}
        </if>
        <if test="user.securitynumber!=null and user.securitynumber != ''">
            and bu.securitynumber = #{user.securitynumber}