<?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.TsPersonFromStatisticMapper">

    <insert id="insertStatisticList">
        INSERT INTO ts_person_from_statistic(scene_type,event_type,from_num,org_id,create_date,registered_num)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.sceneType},#{item.eventType},#{item.fromNum},#{item.orgId},#{item.createDate},#{item.registeredNum})
        </foreach>
    </insert>
    <delete id="deleteByData">
        delete from ts_person_from_statistic where DATE_FORMAT( create_date, '%Y-%m-%d' ) = DATE_FORMAT( NOW( ), '%Y-%m-%d' )
    </delete>
    <select id="selectPersonFrom" resultType="com.huiju.estateagents.statistic.entity.TsPersonFromStatistic">
        SELECT
            *
        FROM
        ts_person_from_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>

    </select>
    <select id="selectPersonFromGroupByDay"
            resultType="com.huiju.estateagents.statistic.entity.TsPersonFromStatistic">
        SELECT
            sum( tp.from_num ) AS from_num,
            sum( tp.registered_num ) AS registered_num,
            DATE_FORMAT( tp.create_date, '%Y-%m-%d' ) AS create_time
        FROM
            ts_person_from_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>
        GROUP BY
            DATE_FORMAT( tp.create_date, '%Y-%m-%d' )
    </select>
    <select id="selectPersonFromGroupByData"
            resultType="com.huiju.estateagents.statistic.entity.TsPersonFromStatistic">
        select ifnull(tp.from_num, 0) as from_num , ifnull(tp.registered_num, 0) AS registered_num , ifnull(tp.create_time, date.date) AS create_time, ifnull(tp.scene_type, 'other') as scene_type from (

        SELECT
        DATE_FORMAT( DATE_SUB( now( ), INTERVAL rownum DAY ), '%Y-%m-%d' ) AS date
        FROM sequence
        WHERE rownum &lt; DATEDIFF(STR_TO_DATE( #{endDate}, '%Y-%m-%d' ), STR_TO_DATE( #{startDate}, '%Y-%m-%d' )) ) as date

        left join (SELECT sum( tp.from_num ) AS from_num, sum( tp.registered_num ) AS registered_num, DATE_FORMAT( tp.create_date, '%Y-%m-%d' ) AS create_time, tp.scene_type
        FROM ts_person_from_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>
        GROUP BY DATE_FORMAT( tp.create_date, '%Y-%m-%d' ), tp.scene_type ORDER BY tp.create_date
        ) as tp on date.date = tp.create_time

    </select>
    <select id="selectNewLinePersonFromGroupByData"
            resultType="com.huiju.estateagents.statistic.entity.TsPersonFromStatistic">
        select ifnull(tp.from_num, 0) as from_num , ifnull(tp.registered_num, 0) AS registered_num , ifnull(tp.create_time, date.date) AS create_time, ifnull(tp.scene_type, 'other') as scene_type from (

        SELECT
        DATE_FORMAT( DATE_SUB( now( ), INTERVAL rownum DAY ), '%Y-%m-%d' ) AS date
        FROM sequence
        WHERE rownum &lt; DATEDIFF(STR_TO_DATE( #{endDate}, '%Y-%m-%d' ), STR_TO_DATE( #{startDate}, '%Y-%m-%d' )) ) as date

        left join (SELECT
        sum( tp.from_num ) AS from_num,
        sum( tp.registered_num ) AS registered_num,
        DATE_FORMAT( tp.create_date, '%Y-%m-%d' ) AS create_time,
        tp.scene_type
        FROM
        ts_person_from_statistic tp
        WHERE
        tp.org_id = #{orgId}
        and tp.scene_type != ''
        and tp.scene_type is not null
        <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>
        GROUP BY
        DATE_FORMAT( tp.create_date, '%Y-%m-%d' )
        ORDER BY tp.create_date
        ) as tp on date.date = tp.create_time
        order by date.date
    </select>
</mapper>
