<?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.bonus.system.basic.dao.SysNoticeMapper">
    <insert id="insertNotice" keyProperty="noticeId" useGeneratedKeys="true" keyColumn="id">
        insert into sys_notice(
        <if test="userId != null and userId != ''">user_id,</if>
        <if test="title != null and title != ''">title,</if>
        <if test="content != null and content != ''">content,</if>
        <if test="type != null and type != ''">type,</if>
        <if test="value != null and value != ''">value,</if>
        <if test="leaveApplyId != null and leaveApplyId != ''">leave_apply_id,</if>
        create_time
        ) values(
        <if test="userId != null and userId != ''">#{userId},</if>
        <if test="title != null and title != ''">#{title},</if>
        <if test="content != null and content != ''">#{content},</if>
        <if test="type != null and type != ''">#{type},</if>
        <if test="value != null and value != ''">#{value},</if>
        <if test="leaveApplyId != null and leaveApplyId != ''">#{leaveApplyId},</if>
        sysdate()
        )
    </insert>
    <insert id="insertUserNotice">
        insert into sys_notice_user(notice_id, user_id,leave_apply_id) values
        <foreach collection="list" item="item" separator=",">
            (#{item.noticeId},#{item.userId},#{item.leaveApplyId})
        </foreach>
    </insert>
    <insert id="insertAttSettingHistoryData">
        replace into att_setting_history(user_id, group_id, current_day) values
        <foreach collection="list" item="item" separator=",">
            (#{item.userId},#{item.groupId},#{item.currentDay})
        </foreach>
    </insert>
    <update id="updateStatus">
        update sys_notice_user
        set is_read='1'
        where notice_id = #{noticeId}

          and is_active = 1
    </update>

    <delete id="delNotice">
        DELETE FROM sys_notice WHERE `leave_apply_id` = #{leaveApplyId};
        DELETE FROM sys_notice_user WHERE `leave_apply_id` = #{leaveApplyId}
    </delete>

    <select id="selectNoticeList" resultType="com.bonus.system.basic.domain.SysNotice" parameterType="com.bonus.system.basic.domain.SysNotice">
        SELECT DISTINCT
        snu.notice_id as noticeId,
        sn.title,
        sn.content,
        sdd.dict_label as type,
        sn.value,
        su.user_name as userName,
        sn.create_time as creatTime,
        snu.is_read as isRead
        FROM
        sys_notice_user snu
        LEFT JOIN sys_notice sn on sn.id=snu.notice_id and sn.is_active=1
        LEFT JOIN sys_user su on su.user_id=sn.user_id and su.is_active=1
        LEFT JOIN sys_user_org suo on suo.user_id=sn.user_id and suo.is_active=1
        LEFT JOIN sys_dict_data sdd on sdd.dict_code=sn.type and sdd.`status`=0
        WHERE snu.is_active=1
        <if test="bean.roleType =='3' || bean.roleType == 3 ">
            and sn.user_id=#{bean.userId}
        </if>
        <if test="bean.isRead != null and bean.isRead != ''">
            and snu.is_read=#{bean.isRead}
        </if>
        <if test="bean.roleType =='2' || bean.roleType == 2 ">
          <if test="bean.OrgListId != null and bean.OrgListId.length > 0">
              and suo.org_id in (
              <foreach collection="bean.OrgListId" item="item" separator=",">
                  #{item}
              </foreach>
              )
          </if>
        </if>
        <if test="bean.userIdBr != null and bean.userIdBr != '' ">
            and  sn.user_id != #{bean.userIdBr}
        </if>
        GROUP BY  snu.notice_id ,
        sn.user_id
        order by sn.create_time desc
    </select>
    <select id="getDictData" resultType="java.lang.Long">
        SELECT dict_code as noticeId
        FROM sys_dict_data
        WHERE dict_label = '轮休申请'
          AND dict_type = 'sys_notice'
          and `status` = 0 limit 1
    </select>
    <select id="getUserIdByModelName" resultType="java.lang.Long">
        SELECT DISTINCT sur.user_id
        FROM sys_menu sm
                 LEFT JOIN sys_role_menu srm ON srm.menu_id = sm.menu_id
            AND srm.is_active = '1'
                 LEFT JOIN sys_user_role sur ON sur.role_id = srm.role_id
            AND sur.is_active = '1'
        WHERE sm.menu_name = #{modelName}
    </select>
    <select id="getUserIdByOrgId" resultType="java.lang.Long">
        SELECT DISTINCT user_id
        FROM sys_organization_head
        WHERE org_id = #{orgId}
    </select>
    <select id="getAttUserIdByOrgId" resultType="java.lang.Long">
        SELECT DISTINCT
        attendance_user_id
        FROM
        sys_organization
        WHERE id IN
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>
    <select id="getAttSettingHistoryDate" resultType="com.bonus.system.att.entity.AttGroupBean">
        SELECT
            *
        FROM
            (
                SELECT
                    group_id,
                    user_id,
                    #{currentDay} as currentDay
                FROM
                    att_group_person_relation
                WHERE
                    #{currentDay} BETWEEN DATE_FORMAT( effective_time, '%Y-%m-%d' )
                        AND DATE_FORMAT( IFNULL(NOW(), expiring_time ), '%Y-%m-%d' )
                ORDER BY
                    user_id,
                    effective_time DESC
                    LIMIT 10000000
            ) aa
        GROUP BY
            user_id
    </select>

    <select id="getAttSettingDate" resultType="java.lang.String">
        SELECT DISTINCT
            current_day
        FROM
            `att_setting_history` WHERE current_day > DATE_ADD(#{startDate}, INTERVAL -30 DAY)
        ORDER BY current_day DESC
    </select>

</mapper>