<?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.statistic.mapper.TsCustomerStatisticDailyMapper">

    <delete id="deleteNowDate">
        delete from ts_customer_statistic_daily
        where customer_type = #{customerType}
        AND date_format( create_date, '%Y-%m-%d' ) = date_format( #{nowDate}, '%Y-%m-%d' )
    </delete>

    <select id="getFollowUpData" resultType="com.huiju.estateagents.statistic.entity.TsCustomerStatisticDaily">
        SELECT
            org_id,
            count( DISTINCT ( customer_id ) ) as customer_num,
            follower_id AS realty_consultant
        FROM
            ta_customer_follow_up_record
        WHERE
            date_format( create_date, '%Y-%m-%d' ) = date_format( #{nowDate}, '%Y-%m-%d' )
        GROUP BY
            org_id,follower_id
    </select>

    <select id="getNewCustomerData" resultType="com.huiju.estateagents.statistic.entity.TsCustomerStatisticDaily">
        SELECT
            org_id,
            count( customer_id ) as customer_num,
			realty_consultant
        FROM
            ta_recommend_customer
        WHERE
             date_format( create_date, '%Y-%m-%d' ) = date_format( #{nowDate}, '%Y-%m-%d' )
             AND verify_status = 1
        GROUP BY
            org_id,realty_consultant
    </select>
    <select id="getVisiteCustomerData" resultType="com.huiju.estateagents.statistic.entity.TsCustomerStatisticDaily">
        SELECT
            count( DISTINCT ( c.customer_id ) ) as customer_num,
            c.org_id,
			c.realty_consultant
        FROM
            ta_activity_dynamic_enlist t
            LEFT JOIN ta_recommend_customer c ON t.person_id = c.person_id
            AND t.building_id = c.building_id
            AND t.org_id = c.org_id
            AND c.verify_status = 1
        WHERE
            t.is_checkin = #{checkin}
            AND date_format( t.create_date, '%Y-%m-%d' ) = date_format( #{nowDate}, '%Y-%m-%d' )
        GROUP BY
            c.org_id,c.realty_consultant
    </select>
    <select id="getDailyList" resultType="com.huiju.estateagents.statistic.entity.TsCustomerStatisticDaily">
        SELECT
          *
        FROM
          ts_customer_statistic_daily
        WHERE
          org_id = #{orgId}
        AND customer_type = #{customerType}
        AND (realty_consultant = #{personId} or realty_consultant = #{userId})
        AND date_format( create_date, '%Y-%m-%d' ) >= date_format( #{beforeDate}, '%Y-%m-%d' )
        AND date_format( create_date, '%Y-%m-%d' ) &lt;= date_format( #{nowDate}, '%Y-%m-%d' )
        order by `day`
    </select>

</mapper>
