<?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.ruoyi.ehsNoticeMessage.mapper.EhsNoticeMessageMapper">

    <resultMap type="EhsNoticeMessage" id="EhsNoticeMessageResult">
            <result property="noticeMessageId"    column="notice_message_id"    />
            <result property="title"    column="title"    />
            <result property="displayContent"    column="display_content"    />
            <result property="fileName"    column="file_name"    />
             <result property="fileTempName"    column="file_temp_name"    />
        <result property="fileTempOriginalName"    column="file_temp_original_name"    />
            <result property="status"    column="status"    />
            <result property="readDeptId"    column="read_dept_id"    />
            <result property="mesgType"    column="mesg_type"    />
            <result property="deptList"    column="dept_list"    />
        <result property="deptListName"    column="dept_list_name"    />
            <result property="allDept"    column="all_dept"    />
            <result property="createDeptId"    column="create_dept_id"    />
            <result property="createBy"    column="create_by"    />
            <result property="createTime"    column="create_time"    />
            <result property="updateBy"    column="update_by"    />
            <result property="updateTime"    column="update_time"    />
            <result property="remark"    column="remark"    />
            <result property="deptId"    column="dept_id"    />
            <result property="createUserId"    column="create_user_id"    />
            <result property="updateUserId"    column="update_user_id"    />
            <result property="fileCount"    column="file_count"    />
            <result property="fileState"    column="file_state"    />
            <result property="fileDeptId"    column="file_dept_id"    />
             <result property="isRead"    column="is_read"    />
        <result property="messageFileId"    column="message_file_id"    />
        <result property="originalName"    column="original_name"    />
        <result property="noticeMessageFileId"    column="notice_message_fele_id"    />
        <result property="issueTime"    column="issue_time"    />
        <result property="myMessageId"    column="my_message_id"    />
        <result property="myUserId"    column="my_user_id"    />
        <result property="myMessageState"    column="my_message_state"    />
    </resultMap>
    <resultMap type="EhsNoticeMessageDept" id="EhsNoticeMessageDeptResult">
        <result property="noticeMessageId"    column="notice_message_id"    />
        <result property="title"    column="title"    />
        <result property="displayContent"    column="display_content"    />
        <result property="fileName"    column="file_name"    />
        <result property="fileTempName"    column="file_temp_name"    />
        <result property="fileTempOriginalName"    column="file_temp_original_name"    />
        <result property="status"    column="status"    />
        <result property="readDeptId"    column="read_dept_id"    />
        <result property="mesgType"    column="mesg_type"    />
        <result property="deptList"    column="dept_list"    />
        <result property="deptListName"    column="dept_list_name"    />
        <result property="allDept"    column="all_dept"    />
        <result property="createDeptId"    column="create_dept_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="deptId"    column="dept_id"    />
        <result property="createUserId"    column="create_user_id"    />
        <result property="updateUserId"    column="update_user_id"    />
        <result property="fileCount"    column="file_count"    />
        <result property="fileState"    column="file_state"    />
        <result property="fileDeptId"    column="file_dept_id"    />
        <result property="isRead"    column="is_read"    />
        <result property="messageFileId"    column="message_file_id"    />
        <result property="originalName"    column="original_name"    />
        <result property="noticeMessageFileId"    column="notice_message_fele_id"    />
        <result property="issueTime"    column="issue_time"    />
        <result property="myMessageId"    column="my_message_id"    />
        <result property="myUserId"    column="my_user_id"    />
        <result property="myMessageState"    column="my_message_state"    />
    </resultMap>

    <sql id="selectEhsNoticeMessageVo">
        select * from ehs_notice_message
    </sql>

    <select id="selectEhsNoticeMessageList" parameterType="EhsNoticeMessageDept" resultMap="EhsNoticeMessageDeptResult">
        select m.notice_message_id, m.create_user_id, m.create_time, m.dept_id,m.title, m.display_content, m.status,m.issue_time
        ,IFNULL(FIND_IN_SET(#{deptId}, m.read_dept_id ),0) is_read,m.mesg_type,m.read_dept_id,m.all_dept,m.file_temp_name,file_temp_original_name,
        (select f.id from ehs_notice_message_file f where  m.notice_message_id = f.notice_message_id and f.dept_ID =#{fileDeptId} ) AS message_file_id,
        (select  f.file_name from ehs_notice_message_file f where  m.notice_message_id = f.notice_message_id and f.dept_ID =#{fileDeptId}) file_name,
        IFNULL((select IFNULL(f.state,0) from ehs_notice_message_file f where  m.notice_message_id = f.notice_message_id and f.dept_ID =#{fileDeptId}),0) file_state,
        (select f.original_name from ehs_notice_message_file f where  m.notice_message_id = f.notice_message_id and f.dept_ID =#{fileDeptId}) original_name,
        (select f.file_size from ehs_notice_message_file f where  m.notice_message_id = f.notice_message_id and f.dept_ID =#{fileDeptId})  file_size,
        (select f.dept_id from ehs_notice_message_file f where  m.notice_message_id = f.notice_message_id and f.dept_ID =#{fileDeptId})   file_dept_id
        ,mym.my_message_id,mym.my_message_state
        from ehs_notice_message m
        left join sys_user e on e.user_id = m.CREATE_USER_ID
        left join sys_dept d on m.dept_id=d.dept_id and d.parent_id!=0
        left join ehs_my_message mym on m.notice_message_id = my_message_key and my_message_type = 1
        <if test="myUserId != null  and myUserId != '' "> and my_user_id = #{myUserId} </if>

        <where>
            <if test="title != null  and title != '' "> and m.title like '%${title}%'</if>
            <if test="displayContent != null  and displayContent != '' "> and m.display_content = #{displayContent}</if>
            <!--<if test="createUserName != null  and createUserName != '' "> and e.name like '%${createUserName}%'</if>-->
            <if test="status != null  and status != '' "> and m.status = #{status}</if>
            <if test="deptId != null  and deptId != '' "> and (FIND_IN_SET(#{deptId}, m.dept_list) or m.all_dept=1) </if>
            <if test="mesgType != null    "> and m.mesg_type = #{mesgType}</if>
            <if test='isRead =="0" '> and FIND_IN_SET(#{deptId}, m.read_dept_id )=0</if>
            <if test="fileState != null  and fileState != '' "> and f.state = #{fileState}</if>
            <if test="noticeMessageId != null  and noticeMessageId != '' "> and f.id = #{noticeMessageId}</if>
            <if test="params.beginIssueTime != null and params.beginIssueTime != '' and params.endIssueTime != null and params.endIssueTime != ''">
                and Issue_Time between #{params.beginIssueTime} and #{params.endIssueTime}</if>
        </where>
    </select>
    <select id="selectEhsNoticeMessageListAdmin" parameterType="EhsNoticeMessage" resultMap="EhsNoticeMessageResult">
        SELECT  m.notice_message_id,   m.create_user_id,    m.create_time,   m.dept_id,m.issue_time,d.dept_name,
        m.title,        m.display_content,        m.STATUS,        m.dept_list,        GROUP_CONCAT( ft.dept_name SEPARATOR ';' ) dept_list_name,
        m.mesg_type,        m.read_dept_id,        m.all_dept,m.file_temp_name,m.file_temp_original_name,     m.remark,
        ( SELECT count( 1 ) FROM ehs_notice_message_file f WHERE f.notice_message_id = m.notice_message_id  and f.state=1 ) file_count
        FROM        ehs_notice_message m
        LEFT JOIN sys_user e ON e.user_id = m.CREATE_USER_ID
        LEFT JOIN sys_dept ft ON FIND_IN_SET( ft.DEPT_ID, m.dept_list )
        left join sys_dept d on e.dept_id = d.dept_id
        <where>
            <if test="title != null  and title != '' "> and m.title like '%${title}%'</if>
            <if test="displayContent != null  and displayContent != '' "> and m.display_content = #{displayContent}</if>
            <!--<if test="createUserName != null  and createUserName != '' "> and e.name like '%${createUserName}%'</if>-->
            <if test="status != null  and status != '' "> and m.status = #{status}</if>
            <if test="deptList != null  and companyId != '' "> and FIND_IN_SET(#{deptList}, m.dept_list)  </if>
            <if test="mesgType != null   "> and m.mesg_type = #{mesgType}</if>
            <if test="createDeptId != null  and createDeptId != '' "> and m.create_dept_id = #{createDeptId}   </if>
            <if test="params.beginIssueTime != null and params.beginIssueTime != '' and params.endIssueTime != null and params.endIssueTime != ''">
                and Issue_Time between #{params.beginIssueTime} and #{params.endIssueTime}</if>
        </where>
        order BY	m.create_time desc
    </select>
    <select id="selectEhsNoticeMessageByNoticeMessageId" parameterType="Long" resultMap="EhsNoticeMessageResult">
            <include refid="selectEhsNoticeMessageVo"/>
            where notice_message_id = #{noticeMessageId}
    </select>

    <update id="updateReadCompany" parameterType="EhsNoticeMessage">
        update ehs_notice_message
        <trim prefix="SET" suffixOverrides=",">
            <if test="readDeptId != null  and readDeptId != ''  ">read_Dept_id =CONCAT(read_Dept_id,#{readDeptId})  </if>
        </trim>
        where notice_message_id = #{noticeMessageId}
    </update>


</mapper>