<?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.crm.manager.dao.mapper.CustomerNewsMapper">
    <select id="searchPageList" resultType="com.crm.rely.backend.core.pojo.view.CustomNewsView"
            parameterType="com.crm.rely.backend.core.entity.custom.news.CustomNewsSearchEntity">
        select custom_news_view.*,custom_info.c_id,user_info.ib_no,agent.ib_no as p_ib_no from custom_news_view
         join custom_info on custom_news_view.custom_id=custom_info.id
        left join user_info on user_info.custom_id=custom_news_view.custom_id
        left join user_info agent on agent.id=custom_info.agent_id
        <where>
            <if test="stamp != null">
                and agent.stamp like binary concat(#{stamp},'%')
            </if>
            <if test="groupId != null and groupId != -1">
                and agent.group_id = #{groupId}
            </if>
            <if test="types!=null">
                and custom_news_view.type in
                <foreach collection="types" index="idx" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="status != null ">
                and custom_news_view.`status` in
                <foreach collection="status" index="idx" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="serial != null and serial!=''">
                and custom_news_view.serial = #{serial}
            </if>
            <if test="startDate != null">
                and custom_news_view.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and custom_news_view.add_time <![CDATA[ < ]]> #{endDate}
            </if>
            <if test="login != null and login!=''">
                and custom_news_view.login like binary concat('%',#{login},'%')
            </if>
            <if test="cId != null and cId!=''">
                and custom_info.c_id =#{cId}
            </if>
        </where>
        order by custom_news_view.add_time desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>
    <select id="searchPageCount" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.custom.news.CustomNewsSearchEntity">
        select count(custom_news_view.id)
        from custom_news_view
        <if test="cId != null">
             join custom_info on custom_news_view.custom_id=custom_info.id
        </if>
        <if test="stamp != null or groupId!=null">
            <if test="cId == null">
                JOIN `custom_info` ON `custom_news_view`.`custom_id` = `custom_info`.`id`
            </if>
            left JOIN `user_info` agent ON `custom_info`.`agent_id` = agent.`id`
        </if>
        <where>
            <if test="stamp != null">
                and agent.stamp like binary concat(#{stamp},'%')
            </if>
            <if test="groupId != null and groupId != -1">
                and agent.group_id = #{groupId}
            </if>
            <if test="types!=null">
                and custom_news_view.type in
                <foreach collection="types" index="idx" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="status != null ">
                and custom_news_view.`status` in
                <foreach collection="status" index="idx" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="serial != null and serial!=''">
                and custom_news_view.serial = #{serial}
            </if>
            <if test="startDate != null">
                and custom_news_view.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and custom_news_view.add_time <![CDATA[ < ]]> #{endDate}
            </if>
            <if test="login != null and login!=''">
                and custom_news_view.login like binary concat('%',#{login},'%')
            </if>
            <if test="cId != null and cId!=''">
                and custom_info.c_id =#{cId}
            </if>
        </where>

    </select>
</mapper>