<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.pcloud.book.advertising.dao.impl.AdvertisingPilotRecordDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.advertising.entity.AdvertisingPilotRecord">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="ad_id" property="adId" jdbcType="BIGINT"/>
        <result column="book_id" property="bookId" jdbcType="BIGINT"/>
        <result column="is_book_group" property="isBookGroup" jdbcType="BIT"/>
        <result column="scene_id" property="sceneId" jdbcType="BIGINT"/>
        <result column="adviser_id" property="adviserId" jdbcType="BIGINT"/>
        <result column="agent_id" property="agentId" jdbcType="BIGINT"/>
        <result column="channel_id" property="channelId" jdbcType="BIGINT"/>
        <result column="official_account_id" property="officialAccountId" jdbcType="BIGINT"/>
        <result column="from_type" property="fromType" jdbcType="VARCHAR"/>
        <result column="from_id" property="fromId" jdbcType="BIGINT"/>
        <result column="wechat_user_id" property="wechatUserId" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="create_day" property="createDay" jdbcType="DATE"/>
        <result column="create_month" property="createMonth" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, ad_id, book_id, is_book_group, scene_id, adviser_id, agent_id, channel_id, official_account_id, from_type,
        from_id, wechat_user_id, create_time, create_day, create_month
    </sql>

    <select id="getById" resultMap="BaseResultMap" parameterType="Long">
        select
        <include refid="Base_Column_List"/>
        from advertising_pilot_record
        where id = #{id}
    </select>

    <select id="getPilotUserNumByAdId" parameterType="map" resultType="Long">
        SELECT
            count(DISTINCT wechat_user_id)
        FROM
            advertising_pilot_record
        WHERE
            ad_id = #{adId}
        <if test="bookId != null">
            AND book_id = #{bookId}
            AND adviser_id = #{adviserId}
            AND channel_id = #{channelId}
        </if>
        <if test="statisMonth != null">
            and create_month = #{statisMonth}
        </if>
    </select>

    <select id="getPilotUserNumByQrcode" parameterType="map" resultType="Long">
        SELECT
            count(DISTINCT wechat_user_id)
        FROM
            advertising_pilot_record
        WHERE
            from_id = #{qrcodeId}
        AND from_type = 'WECHAT_GROUP'
        <if test="adId != null">
            AND ad_id = #{adId}
        </if>
        <if test="statisMonth != null">
            AND create_month = #{statisMonth}
        </if>
        AND wechat_user_id IS NOT NULL
    </select>

    <insert id="insert" parameterType="com.pcloud.book.advertising.entity.AdvertisingPilotRecord" useGeneratedKeys="true" keyProperty="id">
        insert into advertising_pilot_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            ad_id,
            book_id,
            is_book_group,
            scene_id,
            adviser_id,
            agent_id,
            channel_id,
            official_account_id,
            from_type,
            from_id,
            wechat_user_id,
            create_time,
            create_day,
            create_month,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{adId,jdbcType=BIGINT},
            #{bookId,jdbcType=BIGINT},
            #{isBookGroup,jdbcType=BIT},
            #{sceneId,jdbcType=BIGINT},
            #{adviserId,jdbcType=BIGINT},
            #{agentId,jdbcType=BIGINT},
            #{channelId,jdbcType=BIGINT},
            #{officialAccountId,jdbcType=BIGINT},
            #{fromType,jdbcType=VARCHAR},
            #{fromId,jdbcType=BIGINT},
            #{wechatUserId,jdbcType=BIGINT},
            NOW(),
            NOW(),
            DATE_FORMAT(NOW(), '%Y-%m'),
        </trim>
    </insert>

    <update id="update" parameterType="com.pcloud.book.advertising.entity.AdvertisingPilotRecord">
        update advertising_pilot_record
        <set>
            <if test="adId != null">
                ad_id = #{adId,jdbcType=BIGINT},
            </if>
            <if test="bookId != null">
                book_id = #{bookId,jdbcType=BIGINT},
            </if>
            <if test="isBookGroup != null">
                is_book_group = #{isBookGroup,jdbcType=BIT},
            </if>
            <if test="sceneId != null">
                scene_id = #{sceneId,jdbcType=BIGINT},
            </if>
            <if test="adviserId != null">
                adviser_id = #{adviserId,jdbcType=BIGINT},
            </if>
            <if test="agentId != null">
                agent_id = #{agentId,jdbcType=BIGINT},
            </if>
            <if test="channelId != null">
                channel_id = #{channelId,jdbcType=BIGINT},
            </if>
            <if test="officialAccountId != null">
                official_account_id = #{officialAccountId,jdbcType=BIGINT},
            </if>
            <if test="fromType != null">
                from_type = #{fromType,jdbcType=VARCHAR},
            </if>
            <if test="fromId != null">
                from_id = #{fromId,jdbcType=BIGINT},
            </if>
            <if test="wechatUserId != null">
                wechat_user_id = #{wechatUserId,jdbcType=BIGINT},
            </if>
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <select id="getPilotUserNumByMasterId" parameterType="map" resultType="Long">
        SELECT
        COUNT(DISTINCT wechat_user_id)
        FROM
        advertising_pilot_record r
        INNER JOIN advertising_space s ON r.ad_id = s.id
        WHERE
        s.master_id = #{masterId}
    </select>

    <select id="getPilotUserNumByBookId" parameterType="map" resultType="Long">
        SELECT
            count(DISTINCT wechat_user_id)
        FROM
            advertising_pilot_record
        WHERE
            book_id = #{bookId}
            AND adviser_id = #{adviserId}
            AND channel_id = #{channelId}
            AND from_type != 'WECHAT_GROUP'
        <if test="adId != null">
            AND ad_id = #{adId}
        </if>
        <if test="statisMonth != null">
            AND create_month = #{statisMonth}
        </if>
    </select>

    <select id="getPilotUserNumByCPAId" parameterType="long" resultType="long">
        SELECT
        COUNT(DISTINCT wechat_user_id)
        FROM
        advertising_pilot_record r
        INNER JOIN advertising_space s ON r.ad_id = s.id
        WHERE
        s.cpa_id = #{cpaId}
    </select>
</mapper>