1.统计

tangzy

2021-07-21 c421c54a84cc1350bd9b5d6ab545ff3a440b0a1d

1.统计

 src/main/java/org/springblade/modules/dispatcher/entity/Dispatcher.java

@@ -86,6 +86,7 @@
     */
    @TableField("user_ids")
    private String userIds;
    private String jurisdiction;


    /**

 src/main/java/org/springblade/modules/dispatcher/mapper/DispatcherMapper.xml

@@ -10,6 +10,7 @@
        <result column="dispatcher" property="dispatcher"/>
        <result column="dispatcherTime" property="dispatchertime"/>
        <result column="dispatcherAddress" property="dispatcheraddress"/>
        <result column="jurisdiction" property="jurisdiction"/>
    </resultMap>

    <!--派遣记录分页数据-->

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

@@ -261,54 +261,44 @@
    <!--总保安数量-->
    <select id="queryCountB" resultType="java.util.HashMap">
        SELECT C.thisnum,
               C.lastnum,
               d.dept_name as deptname,
               j.dept_name as jurname
        C.lastnum,
        d.dept_name as deptname,
        j.dept_name as jurname
        FROM (
                 SELECT ISNULL(A.thisnum, 0) AS thisnum,
                        ISNULL(B.lastnum, 0) AS lastnum,
                        A.dept_id,
                        A.jurisdiction
                 FROM (
                          SELECT COUNT
                                     (*) AS thisnum,
                                 dept_id,
                                 jurisdiction
                          FROM blade_user
                          WHERE dept_id IN (
                              SELECT i.departmentid
                              FROM sys_information i
                                       INNER JOIN sys_jurisdiction j ON j.id = i.jurisdiction
                              GROUP BY i.departmentid
                          )
                            AND role_id = '1413323482159321089'
                            AND datediff(
                                    MONTH, create_time,
                                           getdate()) = 0
                          GROUP BY dept_id,
                                   jurisdiction
                      ) A
                          FULL JOIN (
                     SELECT COUNT
                                (*) AS lastnum,
                            dept_id,
                            jurisdiction
                     FROM blade_user
                     WHERE dept_id IN (SELECT i.departmentid
                                       FROM sys_information i
                                                LEFT JOIN sys_jurisdiction j ON j.id = i.jurisdiction
                                       GROUP BY i.departmentid)
                       AND role_id = '1413323482159321089'
                       AND datediff(
                               MONTH, create_time,
                                      getdate()) = 1
                     GROUP BY dept_id,
                              jurisdiction
                 ) B ON A.dept_id = B.dept_id) C
                 LEFT JOIN blade_dept d ON C.dept_id = d.id
                 LEFT JOIN sys_jurisdiction j ON j.id = C.jurisdiction where 1=1
        SELECT ISNULL(A.thisnum, 0) AS thisnum,
        ISNULL(B.lastnum, 0) AS lastnum,
        A.dept_id,
        A.jurisdiction
        FROM (
        SELECT COUNT
        (*) AS thisnum,
        dept_id,
        jurisdiction
        FROM blade_user
        WHERE role_id = '1413323482159321089'
        AND datediff(
        MONTH, create_time,
        getdate()) = 0
        GROUP BY dept_id,
        jurisdiction
        ) A
        FULL JOIN (
        SELECT COUNT
        (*) AS lastnum,
        dept_id,
        jurisdiction
        FROM blade_user
        WHERE role_id = '1413323482159321089'
        AND datediff(
        MONTH, create_time,
        getdate()) = 1
        GROUP BY dept_id,
        jurisdiction
        ) B ON A.dept_id = B.dept_id) C
        LEFT JOIN blade_dept d ON C.dept_id = d.id
        LEFT JOIN sys_jurisdiction j ON j.id = C.jurisdiction where 1=1
        <if test="jurisdiction!=null and jurisdiction!=''">
        and C.jurisdiction =#{jurisdiction}
            and C.jurisdiction =#{jurisdiction}
        </if>
        <if test="deptid!=null and deptid!=''">
            and C.dept_id =#{deptid}
@@ -332,14 +322,8 @@
        dept_id,
        jurisdiction
        FROM blade_user
        WHERE dept_id IN (
        SELECT i.departmentid
        FROM sys_information i
        INNER JOIN sys_jurisdiction j ON j.id = i.jurisdiction
        GROUP BY i.departmentid
        )
        AND role_id = '1413323482159321089'
        AND examination = 0
        WHERE  role_id = '1413323482159321089'
        AND examination_type = 0
        AND datediff(
        MONTH, create_time,
        getdate()) = 0
@@ -352,12 +336,8 @@
        dept_id,
        jurisdiction
        FROM blade_user
        WHERE dept_id IN (SELECT i.departmentid
        FROM sys_information i
        LEFT JOIN sys_jurisdiction j ON j.id = i.jurisdiction
        GROUP BY i.departmentid)
        AND role_id = '1413323482159321089'
        AND examination = 0
        WHERE role_id = '1413323482159321089'
        AND examination_type = 0
        AND datediff(
        MONTH, create_time,
        getdate()) = 1
@@ -391,13 +371,7 @@
        dept_id,
        jurisdiction
        FROM blade_user
        WHERE dept_id IN (
        SELECT i.departmentid
        FROM sys_information i
        INNER JOIN sys_jurisdiction j ON j.id = i.jurisdiction
        GROUP BY i.departmentid
        )
        AND role_id = '1413323482159321089'
        WHERE  role_id = '1413323482159321089'
        AND hold = 0
        AND datediff(
        MONTH, create_time,
@@ -411,11 +385,7 @@
        dept_id,
        jurisdiction
        FROM blade_user
        WHERE dept_id IN (SELECT i.departmentid
        FROM sys_information i
        LEFT JOIN sys_jurisdiction j ON j.id = i.jurisdiction
        GROUP BY i.departmentid)
        AND role_id = '1413323482159321089'
        WHERE  role_id = '1413323482159321089'
        AND hold = 0
        AND datediff(
        MONTH, create_time,
@@ -435,152 +405,136 @@

    <!--缴纳社保数量-->
    <select id="queryCountSb" resultType="java.util.HashMap">
        SELECT C.thisnum,
        C.lastnum,
        d.dept_name AS deptname,
        j.dept_name AS jurname
        FROM (
        SELECT ISNULL(A.thisnum, 0) AS thisnum,
        ISNULL(B.lastnum, 0) AS lastnum
        ISNULL(B.lastnum, 0) AS lastnum,
        A.deptid,
        A.jurisdiction
        FROM (
        SELECT COUNT
        (*) AS thisnum,
        deptid
        FROM
        sys_socil
        WHERE
        deptid IN ( SELECT i.departmentid FROM sys_information i LEFT JOIN sys_jurisdiction j ON j.id= i.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1123598813738675201'">
            and i.jurisdiction =#{jurisdiction}
        </if>
        GROUP BY i.departmentid )
        AND datediff(month
        , insuredtime
        , getdate())=0
        <if test="deptid!=null and deptid!=''">
            and deptid =#{deptid}
        </if>
        GROUP BY
        deptid
        deptid,
        jurisdiction
        FROM sys_socil
        WHERE datediff(
        MONTH, insuredtime,
        getdate()) = 0
        GROUP BY deptid,
        jurisdiction
        ) A
        FULL JOIN (
        SELECT COUNT
        (*) AS lastnum,
        deptid
        FROM
        sys_socil
        WHERE
        deptid IN ( SELECT i.departmentid FROM sys_information i LEFT JOIN sys_jurisdiction j ON j.id= i.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1123598813738675201'">
            and i.jurisdiction =#{jurisdiction}
        </if>
        GROUP BY i.departmentid )
        AND datediff(month
        , insuredtime
        , getdate())=1
        <if test="deptid!=null and deptid!=''">
            and deptid =#{deptid}
        </if>
        GROUP BY
        deptid
        deptid,
        jurisdiction
        FROM sys_socil
        WHERE datediff(
        MONTH, insuredtime,
        getdate()) = 1
        GROUP BY deptid,
        jurisdiction
        ) B ON A.deptid = B.deptid
        ) C
        LEFT JOIN blade_dept d ON C.deptid = d.id
        LEFT JOIN sys_jurisdiction j ON j.id = c.jurisdiction where 1=1
        <if test="jurisdiction!=null and jurisdiction!=''">
            and C.jurisdiction =#{jurisdiction}
        </if>
        <if test="deptid!=null and deptid!=''">
            and C.dept_id =#{deptid}
        </if>
    </select>

    <!--服务客户数量-->
    <select id="queryCountKh" resultType="java.util.HashMap">
        SELECT C.thisnum,
        C.lastnum,
        C.dept_id,
        d.dept_name AS deptname,
        j.dept_name AS jurname
        FROM (
        SELECT ISNULL(A.thisnum, 0) AS thisnum,
        ISNULL(B.lastnum, 0) AS lastnum
        ISNULL(B.lastnum, 0) AS lastnum,
        A.dept_id,
        A.jurisdiction
        FROM (
        SELECT COUNT
        (*) AS thisnum,
        dept_id
        FROM
        sys_dispatcher_unit
        WHERE
        dept_id IN ( SELECT i.departmentid FROM sys_information i LEFT JOIN sys_jurisdiction j ON j.id= i.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1123598813738675201'">
            and i.jurisdiction =#{jurisdiction}
        </if>
        GROUP BY i.departmentid )
        AND datediff(month
        , create_time
        , getdate())=0
        <if test="deptid!=null and deptid!=''">
            and dept_id =#{deptid}
        </if>
        GROUP BY
        dept_id
        dept_id,
        jurisdiction
        FROM sys_dispatcher
        WHERE  datediff(
        MONTH, dispatcherTime,
        getdate()) = 0
        GROUP BY dept_id,
        jurisdiction
        ) A
        FULL JOIN (
        SELECT COUNT
        (*) AS lastnum,
        dept_id
        FROM
        sys_dispatcher_unit
        WHERE
        dept_id IN ( SELECT i.departmentid FROM sys_information i LEFT JOIN sys_jurisdiction j ON j.id= i.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1123598813738675201'">
            and i.jurisdiction =#{jurisdiction}
        </if>
        GROUP BY i.departmentid )
        AND datediff(month
        , create_time
        , getdate())=1
        <if test="deptid!=null and deptid!=''">
            and dept_id =#{deptid}
        </if>
        GROUP BY
        dept_id
        dept_id,
        jurisdiction
        FROM sys_dispatcher
        WHERE datediff(
        MONTH, dispatcherTime,
        getdate()) = 1
        GROUP BY dept_id,
        jurisdiction
        ) B ON A.dept_id = B.dept_id
        ) C
        LEFT JOIN blade_dept d ON C.dept_id = d.id
        LEFT JOIN sys_jurisdiction j ON j.id = c.jurisdiction WHERE 1=1
        <if test="jurisdiction!=null and jurisdiction!=''">
            and C.jurisdiction =#{jurisdiction}
        </if>
        <if test="deptid!=null and deptid!=''">
            and C.dept_id =#{deptid}
        </if>
    </select>

    <!--保安派遣数量-->
    <select id="queryCountPq" resultType="java.util.HashMap">
        SELECT ISNULL(A.thisnum, 0) AS thisnum,
        ISNULL(B.lastnum, 0) AS lastnum
        SELECT C.thisnum,
        C.lastnum,
        d.dept_name as deptname,
        j.dept_name as jurname
        FROM (
        SELECT COUNT
        (*) AS thisnum,
        dept_id
        FROM
        sys_dispatcher
        WHERE
        dept_id IN ( SELECT i.departmentid FROM sys_information i LEFT JOIN sys_jurisdiction j ON j.id= i.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1123598813738675201'">
            and i.jurisdiction =#{jurisdiction}
        </if>
        GROUP BY i.departmentid )
        AND datediff(month
        , dispatcherTime
        , getdate())=0
        <if test="deptid!=null and deptid!=''">
            and dept_id =#{deptid}
        </if>
        GROUP BY
        dept_id
        SELECT ISNULL(A.thisnum, 0) AS thisnum,
        ISNULL(B.lastnum, 0) AS lastnum,
        A.dept_id,
        A.jurisdiction
        FROM (
        SELECT SUM(num) AS thisnum,
        dept_id,
        jurisdiction
        FROM sys_dispatcher
        WHERE datediff(
        MONTH, dispatcherTime,
        getdate()) = 0
        GROUP BY dept_id, jurisdiction
        ) A
        FULL JOIN (
        SELECT COUNT
        (*) AS lastnum,
        dept_id
        FROM
        sys_dispatcher
        WHERE
        dept_id IN ( SELECT i.departmentid FROM sys_information i LEFT JOIN sys_jurisdiction j ON j.id= i.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1123598813738675201'">
            and i.jurisdiction =#{jurisdiction}
        SELECT SUM(num) AS lastnum,
        dept_id,
        jurisdiction
        FROM sys_dispatcher
        WHERE datediff(
        MONTH, dispatcherTime,
        getdate()) = 1
        GROUP BY dept_id, jurisdiction
        ) B ON A.dept_id = B.dept_id) C
        LEFT JOIN blade_dept d ON C.dept_id = d.id
        LEFT JOIN sys_jurisdiction j ON j.id = c.jurisdiction where 1=1
        <if test="jurisdiction!=null and jurisdiction!=''">
            and C.jurisdiction =#{jurisdiction}
        </if>
        GROUP BY i.departmentid )
        AND datediff(month
        , dispatcherTime
        , getdate())=1
        <if test="deptid!=null and deptid!=''">
            and dept_id =#{deptid}
            and C.dept_id =#{deptid}
        </if>
        GROUP BY
        dept_id
        ) B ON A.dept_id = B.dept_id
    </select>