<?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.group.dao.impl.AppClickRecordDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.group.entity.AppClickRecord">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="book_group_id" property="bookGroupId" jdbcType="BIGINT"/>
        <result column="classify_id" property="classifyId" jdbcType="BIGINT"/>
        <result column="qrcode_id" property="qrcodeId" jdbcType="BIGINT"/>
        <result column="weixin_group_id" property="weixinGroupId" jdbcType="VARCHAR"/>
        <result column="serve_id" property="serveId" jdbcType="BIGINT"/>
        <result column="serve_type" property="serveType" jdbcType="VARCHAR"/>
        <result column="wechat_user_id" property="wechatUserId" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>


    <sql id="Base_Column_List">
        id, book_group_id, classify_id, qrcode_id, weixin_group_id, serve_id, serve_type, wechat_user_id, create_time
    </sql>

    <insert id="insert" parameterType="com.pcloud.book.group.entity.AppClickRecord" useGeneratedKeys="true"
            keyProperty="id">
        insert into app_click_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            book_group_id,
            classify_id,
            qrcode_id,
            weixin_group_id,
            serve_id,
            serve_type,
            wechat_user_id,
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{bookGroupId,jdbcType=BIGINT},
            #{classifyId,jdbcType=BIGINT},
            #{qrcodeId,jdbcType=BIGINT},
            #{weixinGroupId,jdbcType=VARCHAR},
            #{serveId,jdbcType=BIGINT},
            #{serveType,jdbcType=VARCHAR},
            #{wechatUserId,jdbcType=BIGINT},
            NOW(),
        </trim>
    </insert>

    <!--根据作品或应用id集合获取统计-->
    <select id="getClickStatisticsByServeIds" parameterType="map" resultType="com.pcloud.book.group.dto.AppStatisticsDTO">
        SELECT
        t.serve_id serveId,
        count(1) clickCount
        FROM
        app_click_record t
        WHERE
        t.serve_id IN
        <foreach collection="serveIds" index="index" item="item" close=")" open="(" separator=",">
           #{item}
        </foreach>
        <if test="bookGroupId!=null">
            AND t.book_group_id = #{bookGroupId}
        </if>
        <if test="qrcodeId!=null">
            AND t.qrcode_id = #{qrcodeId}
        </if>
        GROUP BY
        t.serve_id
    </select>

    <select id="getClickStatistics" parameterType="map" resultType="com.pcloud.book.group.vo.ResourcesStatisticVO">
        select serve_id rescourceId, count(1) browseCount from  app_click_record
        where book_group_id = #{bookGroupId} and serve_type = #{type}
        <if test="classifyId != null">
            and classify_id = #{classifyId}
        </if>
        <if test="qrcodeId!=null">
            AND qrcode_id = #{qrcodeId}
        </if>
        group by serve_id
    </select>

    <!--获取最近点击-->
    <select id="getRecentlyClick" parameterType="map" resultType="com.pcloud.book.group.dto.AppClickDTO">
        select
        book_group_id bookGroupId,
        classify_id classifyId,
        qrcode_id qrcodeId,
        weixin_group_id weixinGroupId,
        serve_id serveId,
        serve_type serveType
        from app_click_record
        where wechat_user_id=#{wechatUserId}
        and qrcode_id=#{qrcodeId}
        group by serve_id
        order by create_time desc
        limit #{top}
    </select>

    <select id="getClickTrend" parameterType="com.pcloud.book.group.vo.GroupScanTrendParamVO" resultType="com.pcloud.book.group.vo.GroupScanTrendVO">
        select DATE_FORMAT(create_time, '%Y-%m-%d') date, count(1) scanCount from app_click_record
        where book_group_id = #{bookGroupId}
        <if test="classifyId != null">
            and classify_id = #{classifyId}
        </if>
        <if test="wxGroupId != null">
            and qrcode_id = #{wxGroupId}
        </if>
        and create_time &gt;=
        <if test="startDate == null">
            DATE_FORMAT(SUBDATE(NOW(), INTERVAL #{dayNum} DAY),'%Y-%m-%d')
            AND create_time &lt; DATE_FORMAT(now(), '%Y-%m-%d')
        </if>
        <if test="startDate != null">
            DATE_FORMAT(#{startDate},'%Y-%m-%d')
            AND create_time &lt;= DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        </if>
        GROUP BY date
        order by date
    </select>

    <select id="getResourceClick" parameterType="map" resultType="com.pcloud.book.group.vo.ResourceClickVO">
        SELECT
        t.serve_id rescourceId,
        count(1) clickCount
        FROM
        app_click_record t
        WHERE
        t.serve_id IN
        <foreach collection="rescourceIds" index="index" item="item" close=")" open="(" separator=",">
            #{item}
        </foreach>
        <if test="bookGroupId!=null">
            AND t.book_group_id = #{bookGroupId}
        </if>
        <if test="classifyId!=null">
            AND t.classify_id = #{classifyId}
        </if>
        <if test="qrcodeId!=null">
            AND t.qrcode_id = #{qrcodeId}
        </if>
        <if test="type!=null">
            AND t.serve_type = #{type}
        </if>
        GROUP BY
        t.serve_id
    </select>

    <select id="getRescourceTotalClick" parameterType="map" resultType="long">
        select count(1) FROM app_click_record t
        WHERE t.book_group_id = #{bookGroupId}
        <if test="classifyId!=null">
            AND t.classify_id = #{classifyId}
        </if>
        <if test="qrcodeId!=null">
            AND t.qrcode_id = #{qrcodeId}
        </if>
        <if test="type!=null">
            AND t.serve_type = #{type}
        </if>
    </select>

    <select id="getRescourceIds" parameterType="map" resultType="long">
        select t.serve_id FROM app_click_record t
        WHERE t.book_group_id = #{bookGroupId}
        <if test="classifyId!=null">
            AND t.classify_id = #{classifyId}
        </if>
        <if test="qrcodeId!=null">
            AND t.qrcode_id = #{qrcodeId}
        </if>
        <if test="type!=null">
            AND t.serve_type = #{type}
        </if>
    </select>
</mapper>