<?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.TsUserBehaviorStatisticMapper">
    <delete id="deleteByDate">
        delete from ts_user_behavior_statistic where DATE_FORMAT( create_date, '%Y-%m-%d' ) = DATE_FORMAT( NOW( ), '%Y-%m-%d' )
    </delete>

    <select id="seleUserBehaviorList"
            resultType="com.huiju.estateagents.statistic.entity.TsUserBehaviorStatistic">
                SELECT
            temp_date.date AS create_date,
            temp_date.type_name AS event_name,
            temp_date.type_id AS event_type,
            temp.activityCount AS behavior_num,
            temp.building_id,
            temp.org_id
        FROM
            (
        SELECT
            DATE_FORMAT( DATE_SUB( now( ), INTERVAL a.rownum DAY ), '%Y-%m-%d' ) AS date,
            b.type_id,
            b.type_name,
            a.rownum
        FROM
            sequence a,
            td_biz_event_type b
            ) AS temp_date
            LEFT JOIN (
        SELECT
            COUNT( 1 ) AS activityCount,
            tpvr.event_type,
            DATE_FORMAT( tpvr.visit_time, '%Y-%m-%d' ) AS date,
            tpvr.building_id,
            tp.org_id
        FROM
            ta_person tp
            INNER JOIN ta_person_visit_record tpvr ON tp.person_id = tpvr.person_id
        WHERE
            tp.person_type IN ( 'customer', 'drift', 'estate agent' )
        GROUP BY
            tpvr.event_type,
            DATE_FORMAT( tpvr.visit_time, '%Y-%m-%d' ),
            tp.org_id,
            tpvr.building_id
            ) AS temp ON temp_date.date = temp.date
            AND temp_date.type_id = temp.event_type where temp_date.date = DATE_FORMAT( now( ), '%Y-%m-%d' )
    </select>
    <select id="seleBehaviorListByDate"
            resultType="com.huiju.estateagents.po.PersonPO">
        SELECT
            temp_date.date AS date,
            temp_date.type_name AS activity,
            temp.activityCount AS activityCount
        FROM
        (
            SELECT
            DATE_FORMAT( DATE_SUB( now( ), INTERVAL a.rownum DAY ), '%Y-%m-%d' ) AS date,
            b.type_id,
            b.type_name
            FROM
            sequence a,
            td_biz_event_type b
            <trim prefix="where">
                <if test="startDate != null or endDate != null">
                    a.rownum <![CDATA[ <= ]]> datediff(#{endDate}, #{startDate})
                </if>
            </trim>
        ) AS temp_date
        LEFT JOIN (
            SELECT
            tp.event_type,
            tp.event_name as activity,
            sum( tp.behavior_num )  as activityCount,
            DATE_FORMAT( tp.create_date, '%Y-%m-%d' )  as date
            FROM
            ts_user_behavior_statistic tp
            where tp.org_id = #{orgId}
            <if test="startDate != null or endDate != null">
                AND  DATE_FORMAT( tp.create_date, '%Y-%m-%d' ) BETWEEN #{startDate} and #{endDate}
            </if>
            <if test="startDate == null or endDate == null">
                AND DATE_FORMAT( tp.create_date, '%Y-%m-%d' ) BETWEEN DATE_SUB(now(),INTERVAL 7 DAY) and now()
            </if>
            <if test="buildingId != null and buildingId != ''">
                and tp.building_id = #{buildingId}
            </if>
            GROUP BY
            DATE_FORMAT( tp.create_date, '%Y-%m-%d' ),
            tp.event_type
        ) AS temp ON temp_date.date = temp.date
        AND temp_date.type_id = temp.event_type
        ORDER BY
        temp_date.date ASC
    </select>
</mapper>
