<?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">
<!-- namespace必须指向DAO接口 -->
<mapper namespace="com.jpsoft.shinestar.modules.base.dao.MessageReceiverDAO">
    <resultMap id="MessageReceiverMap" type="com.jpsoft.shinestar.modules.base.entity.MessageReceiver">
        <id property="id" column="id_"/>
        <result property="messageId" column="message_id"/>
        <result property="personId" column="person_id"/>
        <result property="readStatus" column="read_status"/>
        <result property="delFlag" column="del_flag"/>
        <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="name" column="person_name"/>
        <result property="phone" column="phone_"/>
        <result property="sex" column="sex_"/>
        <result property="companyName" column="company_name"/>
        <result property="companyStructName" column="company_struct_name"/>
        <result property="roleName" column="role_name"/>
        <result property="title" column="title_"/>
        <result property="messageCreateTime" column="message_create_time"/>
    </resultMap>
    <insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.MessageReceiver">
        <!--
        <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
            select sys_guid() from dual
        </selectKey>
        -->
        <![CDATA[
		insert into base_message_receiver
	    (id_,message_id,person_id,read_status,del_flag,create_by,create_time,update_by,update_time)
		values
		(
            #{id,jdbcType=VARCHAR}
            ,#{messageId,jdbcType=VARCHAR}
            ,#{personId,jdbcType=NUMERIC}
            ,#{readStatus,jdbcType= NUMERIC }
            ,#{delFlag,jdbcType= NUMERIC }
            ,#{createBy,jdbcType=VARCHAR}
            ,#{createTime,jdbcType= TIMESTAMP }
            ,#{updateBy,jdbcType=VARCHAR}
            ,#{updateTime,jdbcType= TIMESTAMP }
		)
	]]>
    </insert>
    <delete id="delete" parameterType="string">
        delete from base_message_receiver where id_=#{id,jdbcType=VARCHAR}
    </delete>
    <update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.MessageReceiver">
        update base_message_receiver
        <set>
            <if test="messageId!=null">
                message_id=#{messageId,jdbcType=VARCHAR},
            </if>
            <if test="personId!=null">
                person_id=#{personId,jdbcType=NUMERIC},
            </if>
            <if test="readStatus!=null">
                read_status=#{readStatus,jdbcType= NUMERIC },
            </if>
            <if test="delFlag!=null">
                del_flag=#{delFlag,jdbcType= NUMERIC },
            </if>
            <if test="createBy!=null">
                create_by=#{createBy,jdbcType=VARCHAR},
            </if>
            <if test="createTime!=null">
                create_time=#{createTime,jdbcType= TIMESTAMP },
            </if>
            <if test="updateBy!=null">
                update_by=#{updateBy,jdbcType=VARCHAR},
            </if>
            <if test="updateTime!=null">
                update_time=#{updateTime,jdbcType= TIMESTAMP },
            </if>
        </set>
        where id_=#{id}
    </update>
    <select id="get" parameterType="string" resultMap="MessageReceiverMap">
        select
        id_,message_id,person_id,read_status,del_flag,create_by,create_time,update_by,update_time from
        base_message_receiver where id_=#{0}
    </select>
    <select id="exist" parameterType="string" resultType="int">
        select count(*) from base_message_receiver where id_=#{0}
    </select>
    <select id="list" resultMap="MessageReceiverMap">
        select * from base_message_receiver
    </select>
    <select id="search" parameterType="hashmap" resultMap="MessageReceiverMap">
        select
        t.*,
        a.name_ as person_name,
        a.sex_,
        a.phone_,
        c.name_ as company_name,
        d.structure_name as company_struct_name,
        e.person_role_name as role_name
        from base_message_receiver t
        inner join base_person_info a on t.person_id = a.id_
        inner join base_person_popedom b on a.id_ = b.person_id and b.del_flag=0
        inner join base_company_info c on b.company_id = c.id_ and c.del_flag=0
        left join base_company_structure_info d on b.company_structure_id = d.id_ and d.del_flag=0
        left join base_person_role_info e on b.person_role_id = e.id_ and e.del_flag=0
        where t.message_id = #{searchParams.messageId}
        and a.del_flag=0
        <if test="searchParams.companyId != null">
            and b.company_id = #{searchParams.companyId}
        </if>
        <if test="searchParams.companyStructureId != null">
            and b.company_structure_id = #{searchParams.companyStructureId}
        </if>
        <if test="searchParams.roleId != null">
            and b.person_role_id = #{searchParams.roleId}
        </if>
        <if test="searchParams.personName != null">
            and a.name_ like #{searchParams.personName}
        </if>
        <if test="searchParams.readStatus != null">
            and t.read_status = #{searchParams.readStatus}
        </if>
        <foreach item="sort" collection="sortList" open="order by" separator=",">
            ${sort.name} ${sort.order}
        </foreach>
    </select>
    <select id="countUnReadByPersonIdAndType" resultType="int">
        select count(*) from base_message_info a
        inner join base_message_receiver b on a.id_ = b.message_id
        where a.del_flag=0
        and b.del_flag=0
        and b.person_id=#{personId}
        <if test="type != null">
        and a.type_=#{type}
        </if>
        and b.read_status=0
    </select>
    <select id="messagePageSearch" resultMap="MessageReceiverMap">
        select
        b.*,
        a.title_,
        a.create_time as message_create_time
        from base_message_info a
        inner join base_message_receiver b on a.id_ = b.message_id
        where a.del_flag=0
        and b.del_flag=0
        and b.person_id=#{searchParams.personId}
        and a.type_=#{searchParams.type}
        and a.status_=#{searchParams.status}
        <if test="searchParams.excludeMessageId != null">
            <![CDATA[
                and a.id_ <> #{searchParams.excludeMessageId}
            ]]>
        </if>
        <foreach item="sort" collection="sortList" open="order by" separator=",">
            ${sort.name} ${sort.order}
        </foreach>
    </select>
    <select id="findByMessageIdAndPersonId" resultMap="MessageReceiverMap">
        select * from
        base_message_receiver
        where person_id=#{personId}
        and message_id=#{messageId}
        and del_flag=0
        order by create_time desc
        limit 1
    </select>
</mapper>