mybatis操作

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.thinkgem.jeesite.modules.oa.dao.SignStatisticDao">

    <!-- 查询是否公休假日及法定假日 -->
    <select id="checkofholiday" resultType="String">
    <![CDATA[
        SELECT   
            CASE 
                WHEN 
                    (WEEKDAY(#{date}) IN (5,6) 
                        AND 
                        (SELECT COUNT(ID)
                            FROM SYS_HOLIDAY
                        WHERE DATE = #{date} 
                            AND FLAG=2
                            AND IFNULL(DEL_FLAG,'0') <> '1'
                        )<1)
                    OR  
                    (SELECT COUNT(ID) 
                        FROM SYS_HOLIDAY 
                        WHERE DATE =  #{date} 
                            AND FLAG=1 
                            AND IFNULL(DEL_FLAG,'0') <> '1')>0  
                THEN 1
                ELSE 0 
            END
    ]]> 
    </select>

    <!-- 获取角色id -->
    <select id="getEnname" resultType="String">
    <![CDATA[
        SELECT
              ROLE.ENNAME
            FROM SYS_USER_ROLE USERROLE,
              SYS_ROLE ROLE
            WHERE USERROLE.USER_ID =  #{userIdOfHimself}
                AND ROLE.DEL_FLAG <> '1'
                AND USERROLE.ROLE_ID = ROLE.ID
    ]]>
    </select>

    <!-- 返回检察长用户集  -->
    <select id="getAllUsers" resultType="String">
    <![CDATA[
        SELECT
          ID
        FROM SYS_USER 
        WHERE
        IFNULL(DEL_FLAG,'0') <> '1'
    ]]>
    </select>

    <!-- 返回副检察长用户集  -->
    <select id="getPartOfUsers" resultType="String">
    <![CDATA[
        SELECT
          USER2.ID
        FROM SYS_USER USER1,
          SYS_AUDITOR_OFFICE OFFICE,
          SYS_USER USER2
        WHERE IFNULL(USER1.DEL_FLAG,'0') <> '1'
            AND IFNULL(USER2.DEL_FLAG,'0') <> '1'
            AND USER1.ID = OFFICE.USER_ID
            AND USER2.OFFICE_ID = OFFICE.OFFICE_ID
            AND USER1.ID = #{userIdOfHimself}
    ]]>
    </select>

    <!-- 返回部门负责人用户集合  -->
    <select id="getDeptOfUsers" resultType="String">
    <![CDATA[
        SELECT
          ID
        FROM SYS_USER
        WHERE IFNULL(DEL_FLAG,'0') <> '1'
            AND OFFICE_ID = (SELECT
                               OFFICE_ID
                             FROM SYS_USER
                             WHERE IFNULL(DEL_FLAG,'0') <> '1'
                                 AND ID = #{userIdOfHimself})
    ]]>
    </select>

    <!-- 返回查询的用户集  -->
    <select id="getUserById" resultType="String">
        SELECT
          ID
        FROM SYS_USER 
    <![CDATA[
        WHERE IFNULL(DEL_FLAG,'0') <> '1'
            AND IFNULL(NEEDSIGN,'0') = '1'
    ]]>
         <if test="userId != null and userId != ''">
            AND ID = #{userId}
         </if>
         <if test="officeId != null and officeId != ''">
            AND OFFICE_ID = #{officeId}
         </if>
    </select>

    <select id="selectAmHoliday" resultType="SignStatistic">
        <![CDATA[
        SELECT
          OS.USER_ID      USERID,
          SU.NAME         USERNAME,
          SO.NAME         OFFICENAME,
          OS.SIGN_DATE    SIGNDATE,
          OS.SIGN_TIME    SIGNTIME,
          OS.ISLATE       ISLATE,
          OS.ISWORKOVER   ISWORKOVER
        FROM OA_SIGN OS,
          SYS_USER SU,
          SYS_OFFICE SO
        WHERE OS.USER_ID = SU.ID
            AND SU.OFFICE_ID = SO.ID
            AND IFNULL(OS.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.DEL_FLAG,'0') <> '1'
            AND IFNULL(SO.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.NEEDSIGN,'0') = '1'
            AND OS.ISWORKOVER = '1'
            AND OS.AMORPM = '0'
            ]]>
            <if test="userId != null and userId != ''">
                AND OS.USER_ID = #{userId}
            </if>
            <if test="day != null and day != ''">
                AND OS.SIGN_DATE = #{day}
            </if>
    </select>

    <select id="selectPmHoliday" resultType="SignStatistic">
    <![CDATA[
        SELECT
          OS.USER_ID      USERID,
          SU.NAME         USERNAME,
          SO.NAME         OFFICENAME,
          OS.SIGN_DATE    SIGNDATE,
          OS.SIGN_TIME    SIGNTIME,
          OS.ISLATE       ISLATE,
          OS.ISWORKOVER   ISWORKOVER
        FROM OA_SIGN OS,
          SYS_USER SU,
          SYS_OFFICE SO
        WHERE OS.USER_ID = SU.ID
            AND SU.OFFICE_ID = SO.ID
            AND IFNULL(OS.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.DEL_FLAG,'0') <> '1'
            AND IFNULL(SO.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.NEEDSIGN,'0') = '1'
            AND OS.ISWORKOVER = '1'
            AND OS.AMORPM = '1'
            ]]>
            <if test="userId != null and userId != ''">
                AND OS.USER_ID = #{userId}
            </if>
            <if test="day != null and day != ''">
                AND OS.SIGN_DATE = #{day}
            </if>
    </select>

    <select id="selectAmSign" resultType="SignStatistic">
    <![CDATA[
        SELECT
          OS.USER_ID      USERID,
          SU.NAME         USERNAME,
          SO.NAME         OFFICENAME,
          OS.SIGN_DATE    SIGNDATE,
          OS.SIGN_TIME    SIGNTIME,
          OS.ISLATE       ISLATE,
          OS.ISWORKOVER   ISWORKOVER
        FROM OA_SIGN OS,
          SYS_USER SU,
          SYS_OFFICE SO
        WHERE OS.USER_ID = SU.ID
            AND SU.OFFICE_ID = SO.ID
            AND IFNULL(OS.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.DEL_FLAG,'0') <> '1'
            AND IFNULL(SO.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.NEEDSIGN,'0') = '1'
            AND OS.ISWORKOVER = '0'
            AND OS.AMORPM = '0'
        ]]>
            <if test="userId != null and userId != ''">
                AND OS.USER_ID = #{userId}
            </if>
            <if test="day != null and day != ''">
                AND OS.SIGN_DATE = #{day}
            </if>
    </select>

    <select id="selectPmSign" resultType="SignStatistic">
    <![CDATA[
        SELECT
          OS.USER_ID      USERID,
          SU.NAME         USERNAME,
          SO.NAME         OFFICENAME,
          OS.SIGN_DATE    SIGNDATE,
          OS.SIGN_TIME    SIGNTIME,
          OS.ISLATE       ISLATE,
          OS.ISWORKOVER   ISWORKOVER
        FROM OA_SIGN OS,
          SYS_USER SU,
          SYS_OFFICE SO
        WHERE OS.USER_ID = SU.ID
            AND SU.OFFICE_ID = SO.ID
            AND IFNULL(OS.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.DEL_FLAG,'0') <> '1'
            AND IFNULL(SO.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.NEEDSIGN,'0') = '1'
            AND OS.ISWORKOVER = '0'
            AND OS.AMORPM = '1'
        ]]>
            <if test="userId != null and userId != ''">
                AND OS.USER_ID = #{userId}
            </if>
            <if test="day != null and day != ''">
                AND OS.SIGN_DATE = #{day}
            </if>
    </select>

    <select id="selectAmOutWork" resultType="SignStatistic">
    <![CDATA[
        SELECT
          SU.ID           USERID,
          SU.NAME         USERNAME,
          SO.NAME         OFFICENAME,
          #{day}          SIGNDATE,
          '上午'            SIGNTIME,
          '2'              ISLATE,
          ''              ISWORKOVER
        FROM SYS_USER SU,
          SYS_OFFICE SO
        WHERE SU.OFFICE_ID = SO.ID
            AND IFNULL(SU.DEL_FLAG,'0') <> '1'
            AND IFNULL(SO.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.NEEDSIGN,'0') = '1'
        ]]>
            <if test="userId != null and userId != ''">
                AND SU.ID = #{userId}
            </if>
    </select>

    <select id="selectPmOutWork" resultType="SignStatistic">
    <![CDATA[
        SELECT
          SU.ID           USERID,
          SU.NAME         USERNAME,
          SO.NAME         OFFICENAME,
          #{day}          SIGNDATE,
          '下午'            SIGNTIME,
          '2'              ISLATE,
          ''              ISWORKOVER
        FROM SYS_USER SU,
          SYS_OFFICE SO
        WHERE SU.OFFICE_ID = SO.ID
            AND IFNULL(SU.DEL_FLAG,'0') <> '1'
            AND IFNULL(SO.DEL_FLAG,'0') <> '1'
            AND IFNULL(SU.NEEDSIGN,'0') = '1'
        ]]>
            <if test="userId != null and userId != ''">
                AND SU.ID = #{userId}
            </if>
    </select>

    <!-- 查询当前日期 -->
    <select id="Tody" resultType="String">
        <![CDATA[
            SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY)
        ]]>
    </select>

    <!-- 查询个人假期记录 -->
    <select id="selectHolidays" resultType="SignStatistic">
        <![CDATA[
        SELECT 
             USERID,
             USERNAME,
             OFFICENAME,
             DATE,
             TIME,
             HO.AMORPM AMORPM,
             SIGNSTARTS,
             b.sign_time SIGNOUTTIME,
             (CASE WHEN b.sign_time IS NULL THEN '未签退' WHEN b.issign='1' THEN '早退'  ELSE '正常' END)    signoutstate,
             (CASE WHEN b.sign_time IS NULL THEN '1' WHEN b.issign='1' THEN '2' ELSE '3' END)    signoutflag
        FROM
            (       
                    SELECT
                        u.name         USERNAME,
                        u.id           USERID,
                        o.name         OFFICENAME,
                        #{day}        DATE,
                        '0'           AMORPM,
                        s.sign_time     TIME,
                        (CASE WHEN s.sign_time IS NULL THEN '未签到' WHEN s.islate='1' THEN '迟到'  ELSE '正常' END)    SIGNSTARTS
                    FROM sys_user u
                        LEFT JOIN oa_sign s
                          ON u.id = s.user_id
                        AND u.del_flag <> '1'
                        AND s.del_flag <> '1'
                        AND s.sign_date = #{day}
                        AND s.amorpm = '0',
                        sys_office o
                    WHERE o.del_flag <> '1'
                        AND u.office_id = o.id 
                    ]]>
                        AND u.id IN
                        <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                            #{user}
                        </foreach>
                UNION ALL 
                    <![CDATA[
                      SELECT
                            u.name         USERNAME,
                            u.id           USERID,
                            o.name         OFFICENAME,
                            #{day}        DATE,
                            '1'           AMORPM,
                            s.sign_time     TIME,
                            (CASE WHEN s.sign_time IS NULL THEN '未签到' WHEN s.islate='1' THEN '迟到'  ELSE '正常' END)    SIGNSTARTS
                        FROM sys_user u
                            LEFT JOIN oa_sign s
                              ON u.id = s.user_id
                            AND u.del_flag <> '1'
                            AND s.del_flag <> '1'
                            AND s.sign_date = #{day}
                            AND s.amorpm = '1',
                            sys_office o
                        WHERE o.del_flag <> '1'
                            AND u.office_id = o.id 
                        ]]>
                            AND u.id IN
                            <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                                #{user}
                            </foreach>
                   ) AS HO LEFT JOIN oa_signout b ON HO.AMORPM=b.amorpm and HO.DATE=b.sign_date and HO.USERID=b.user_id
                   <![CDATA[AND b.del_flag <> '1']]>
            ORDER BY HO.USERID
    </select>

    <!-- 查询个人工作日记录 -->
    <select id="selectWorkdays" resultType="SignStatistic">
        <![CDATA[
        SELECT
                USERID,
                USERNAME,
                OFFICENAME,
                DATE,
                TIME,
                WD.AMORPM AMORPM,
                SIGNSTARTS,
                b.sign_time SIGNOUTTIME,
                (CASE WHEN b.sign_time IS NULL THEN '未签退' WHEN b.issign='1' THEN '早退'  ELSE '正常' END)    signoutstate,
                (CASE WHEN b.sign_time IS NULL THEN '1' WHEN b.issign='1' THEN '2' ELSE '3' END)    signoutflag
        FROM  (
                SELECT
                    SU.ID           USERID,
                    SU.NAME         USERNAME,
                    SO.NAME         OFFICENAME,
                    SI.SIGN_DATE    DATE,
                    SI.SIGN_TIME    TIME,
                    SI.amorpm       AMORPM,
                    (CASE WHEN SI.ISLATE ='1' THEN '迟到' ELSE '正常' END)    SIGNSTARTS
                FROM SYS_USER SU,
                    SYS_OFFICE SO,
                    OA_SIGN SI
                WHERE SU.OFFICE_ID = SO.ID
                      AND SU.ID = SI.USER_ID
                      AND IFNULL(SU.DEL_FLAG,'0') <> '1'
                      AND IFNULL(SO.DEL_FLAG,'0') <> '1'
                      AND IFNULL(SI.DEL_FLAG,'0') <> '1'
                      AND SI.SIGN_DATE = #{day}
                      AND SI.USER_ID IN
                ]]>
                <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                     #{user}
                </foreach>
                     AND SI.USER_ID NOT IN (
                     SELECT SE.USER_ID USER_ID 
                         FROM OA_SECTION SE 
                         WHERE IFNULL(SE.DEL_FLAG,'0')<![CDATA[ <> ]]>'1'
                             AND SE.DATE = #{day}
                             AND SE.USER_ID IN 
                        <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                             #{user}
                        </foreach>
                            AND
                            (CASE WHEN SI.amorpm='1' THEN SE.AMORPM ='pm' ELSE SE.AMORPM ='am' END)
                )
                <![CDATA[
                UNION ALL 
                SELECT
                     SU.ID            USERID,
                     SU.NAME          USERNAME,
                     SO.NAME          OFFICENAME,
                     SE.DATE          DATE,
                     ''               TIME,
                     (CASE WHEN SE.AMORPM='pm' THEN '1' ELSE '0' END)    AMORPM,
                     (CASE WHEN SE.FLAG ='gongchu' THEN '公出' ELSE '请假' END)    SIGNSTARTS
                     FROM SYS_USER SU,
                     SYS_OFFICE SO,
                     OA_SECTION SE
                     WHERE SU.OFFICE_ID = SO.ID
                       AND SU.ID = SE.USER_ID
                       AND IFNULL(SU.DEL_FLAG,'0') <> '1'
                       AND IFNULL(SO.DEL_FLAG,'0') <> '1'
                       AND IFNULL(SE.DEL_FLAG,'0') <> '1'
                       AND SE.DATE = #{day}
                       AND SE.USER_ID IN
            ]]>
            <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                 #{user}
            </foreach>
            <![CDATA[
                UNION ALL 
                SELECT
                    SU.ID           USERID,
                    SU.NAME         USERNAME,
                    SO.NAME         OFFICENAME,
                    #{day}         DATE,
                    ''             TIME,
                    '0'           AMORPM,
                    '未签到'          SIGNSTARTS
                FROM SYS_USER SU ,
                    SYS_OFFICE SO
                WHERE SU.OFFICE_ID = SO.ID
                    AND IFNULL(SU.DEL_FLAG,'0') <> '1'
                    AND IFNULL(SO.DEL_FLAG,'0') <> '1'
                    AND SU.ID IN 
            ]]>
            <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                 #{user}
            </foreach>
            <![CDATA[
                    AND SU.ID NOT IN (
                         SELECT SI.USER_ID USER_ID 
                         FROM OA_SIGN SI
                             WHERE IFNULL(SI.DEL_FLAG,'0') <> '1'
                             AND SI.SIGN_DATE = #{day}
                             AND SI.USER_ID IN 
                            ]]>
                            <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                                 #{user}
                            </foreach>
                            <![CDATA[
                             AND SI.AMORPM = '0'
                         UNION 
                         SELECT SE.USER_ID USER_ID 
                         FROM OA_SECTION SE 
                             WHERE IFNULL(SE.DEL_FLAG,'0') <> '1'
                             AND SE.DATE = #{day}
                             AND SE.USER_ID IN 
                            ]]>
                            <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                                 #{user}
                            </foreach>
                            <![CDATA[
                             AND SE.AMORPM = 'am')
                UNION ALL 
                SELECT
                    SU.ID           USERID,
                    SU.NAME         USERNAME,
                    SO.NAME         OFFICENAME,
                    #{day}         DATE,
                    ''            TIME,
                    '1'          AMORPM,
                    '未签到'          SIGNSTARTS
                FROM SYS_USER SU ,
                    SYS_OFFICE SO
                WHERE SU.OFFICE_ID = SO.ID
                     AND IFNULL(SU.DEL_FLAG,'0') <> '1'
                     AND IFNULL(SO.DEL_FLAG,'0') <> '1'
                     AND SU.ID IN 
                    ]]>
                    <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                         #{user}
                    </foreach>
                    <![CDATA[
                     AND SU.ID NOT IN (
                         SELECT SI.USER_ID USER_ID 
                         FROM OA_SIGN SI
                         WHERE IFNULL(SI.DEL_FLAG,'0') <> '1'
                             AND SI.SIGN_DATE = #{day}
                             AND SI.USER_ID IN 
                        ]]>
                        <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                             #{user}
                        </foreach>
                        <![CDATA[
                             AND SI.AMORPM = '1'
                         UNION 
                         SELECT SE.USER_ID USER_ID 
                         FROM OA_SECTION SE 
                         WHERE IFNULL(SE.DEL_FLAG,'0') <> '1'
                             AND SE.DATE = #{day}
                             AND SE.USER_ID IN 
                        ]]>
                        <foreach collection="userList" item="user" index="index" open="(" close=")" separator=",">
                             #{user}
                        </foreach>
                        <![CDATA[
                             AND SE.AMORPM = 'pm')
            ) AS WD LEFT JOIN oa_signout b ON WD.AMORPM=b.amorpm and WD.DATE=b.sign_date and WD.USERID=b.user_id
            AND b.del_flag <> '1'
            ORDER BY WD.USERID,WD.OFFICENAME  
        ]]>
    </select>

</mapper>