<?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.huiju.estateagents.mapper.TaShareCountMapper">
    <select id="countShare" resultType="java.lang.Integer">
        select count(1) from ta_share_count t
        left join ta_person a on t.person_id = a.person_id
        where
        t.person_id = #{personId}
        and a.org_id = #{orgId}
        <if test="startCreateDate != null and startCreateDate != ''">
            and  date_format(t.create_date,'%Y-%m-%d') >= date_format(#{startCreateDate},'%Y-%m-%d')
        </if>
        <if test="endCreateDate != null and endCreateDate != ''">
            and  date_format(t.create_date,'%Y-%m-%d') &lt;= date_format(#{endCreateDate},'%Y-%m-%d')
        </if>
    </select>
    <select id="selectListByParams" resultType="com.huiju.estateagents.entity.TaShareCount">
        select

        case
        when a.title != null || a.title != '' then a.title
        when b.title != null || b.title != '' then b.title
        when c.activity_name != null || c.activity_name != '' then c.activity_name
        when d.`name` != null || d.name != '' then d.`name`
        when e.building_name != null || e.building_name != '' then e.building_name
        when f.news_name != null || f.news_name != '' then f.news_name
        when g.`name` != null || g.`name` != '' then g.`name`
        when h.sales_batch_name != null || h.sales_batch_name != '' then h.sales_batch_name
        else '空'
        end as shareTitle,t.create_date
        from ta_share_count t
        left join ta_building_dynamic a on t.be_share = a.dynamic_id and t.tagert_type = 'activity'
        left join ta_help_activity b on t.be_share = b.help_activity_id and t.tagert_type = 'help'
        left join ta_share_activity c on t.be_share = c.group_activity_id and t.tagert_type = 'group'
        LEFT JOIN ta_drainage d on t.be_share = d.drainage_id and t.tagert_type = 'h5'
        left join ta_building e on t.be_share = e.building_id and t.tagert_type = 'project'
        LEFT JOIN ta_news f on t.be_share = f.news_id and t.tagert_type = 'news'
        LEFT JOIN ta_sales_batch h on t.be_share = h.sales_batch_id and t.tagert_type like CONCAT('house' , '%')
        left JOIN ta_person g on t.be_share = g.person_id and t.tagert_type = 'consultant'
        where t.person_id = #{personId}
        <if test="shareType != null and shareType != ''">
            and (t.tagert_type = #{shareType} || t.tagert_type like CONCAT(#{shareType} , '%'))
        </if>
        <if test="shareTitle != null and shareTitle != ''">
            and
            (
                a.title like CONCAT('%', #{shareTitle} , '%') or
                b.title like CONCAT('%', #{shareTitle} , '%') or
                c.activity_name like CONCAT('%', #{shareTitle} , '%') or
                d.`name` like CONCAT('%', #{shareTitle} , '%') or
                e.building_name like CONCAT('%', #{shareTitle} , '%') or
                f.news_name like CONCAT('%', #{shareTitle} , '%') or
                g.`name` like CONCAT('%', #{shareTitle} , '%') or
                h.sales_batch_name like CONCAT('%', #{shareTitle} , '%')
            )
        </if>
        order by t.create_date desc
    </select>

    <select id="selectClickListByParams" resultType="com.huiju.estateagents.entity.TaShareCount">
        SELECT
            case
                when g.title != null || g.title != '' then g.title
                when b.title != null || b.title != '' then b.title
                when c.activity_name != null || c.activity_name != '' then c.activity_name
                when d.`name` != null || d.name != '' then d.`name`
                when e.building_name != null || e.building_name != '' then e.building_name
                when f.news_name != null || f.news_name != '' then f.news_name
                when h.sales_batch_name != null || h.sales_batch_name !='' then h.sales_batch_name
                WHEN y.`name` != NULL || y.`name` != '' THEN y.`name`
                else '空'
                end as shareTitle,
                        z.phone as personPhone,
                        z.nickName as personName,
                        t.create_date

        FROM
            ta_share_person_from t

        left join ta_building_dynamic g on t.target_id = g.dynamic_id and t.target_type = 'dynamic_share'
                left join ta_help_activity b on t.target_id = b.help_activity_id and t.target_type = 'help_share'
                left join ta_share_activity c on t.target_id = c.group_activity_id and t.target_type = 'group_share'
                LEFT JOIN ta_drainage d on t.target_id = d.drainage_id and t.target_type = 'h5_share'
                left join ta_building e on t.target_id = e.building_id and t.target_type = 'building_share'
                LEFT JOIN ta_news f on t.target_id = f.news_id and t.target_type = 'news_share'
                left join ta_sales_batch h on t.target_id = h.sales_batch_id and t.target_type = 'house_share'
        LEFT JOIN ta_person a ON t.share_person = a.person_id
        LEFT JOIN ta_person y on t.share_person = y.user_id
        LEFT JOIN ta_person z ON t.person_id = z.person_id
        WHERE
            (t.share_person = (select user_id from ta_person where person_id = #{personId}) or t.share_person = #{personId})
        AND t.org_id = #{orgId}
        AND t. STATUS = 1
        AND t.person_id != #{personId}
        <if test="shareType != null and shareType != ''">
            and t.target_type = #{shareType}
        </if>
        <if test="shareTitle != null and shareTitle != ''">
            and
            (
            g.title like CONCAT('%', #{shareTitle} , '%') or
            b.title like CONCAT('%', #{shareTitle} , '%') or
            c.activity_name like CONCAT('%', #{shareTitle} , '%') or
            d.`name` like CONCAT('%', #{shareTitle} , '%') or
            e.building_name like CONCAT('%', #{shareTitle} , '%') or
            f.news_name like CONCAT('%', #{shareTitle} , '%') or
            a.`name` like CONCAT('%', #{shareTitle} , '%')
            h.sales_batch_name like like CONCAT('%', #{shareTitle} , '%')
            )
        </if>
        GROUP BY
            t.target_id,t.target_type,t.person_id,t.share_person order by t.create_date desc
    </select>
</mapper>
