<?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>

    <!--根据类型按照时间获取资源服务点击人数-->
    <select id="getAppClickByTime" parameterType="map" resultType="com.pcloud.book.group.dto.CountAndTimeDTO">
        SELECT
        create_time time,
        COUNT(wechat_user_id) count
        FROM
        (
        SELECT
        t.wechat_user_id wechat_user_id,
        <if test="timeType==1">
            DATE_FORMAT(t.create_time, '%Y-%m-%d') create_time
        </if>
        <if test="timeType==2">
            DATE_FORMAT(t.create_time, '%Y-%m-%d %H') create_time
        </if>
        FROM
        book_group t1
        STRAIGHT_JOIN app_click_record t ON t.book_group_id = t1.id
        WHERE
        1 = 1
        <if test="joinGroupType!=null">
            AND t1.join_group_type = #{joinGroupType}
        </if>
        <if test="startTime!=null">
            AND t.create_time >= #{startTime}
        </if>
        <if test="endTime!=null">
            AND t.create_time &lt;= #{endTime}
        </if>
        GROUP BY
        t.wechat_user_id,
        <if test="timeType==1">
            DATE_FORMAT(t.create_time, '%Y-%m-%d')
        </if>
        <if test="timeType==2">
            DATE_FORMAT(t.create_time, '%Y-%m-%d %H')
        </if>
        ORDER BY
        t.create_time ASC
        ) tt
        <if test="timeType==1">
            GROUP BY DATE_FORMAT(tt.create_time,'%Y-%m-%d')
        </if>
        <if test="timeType==2">
            GROUP BY DATE_FORMAT(tt.create_time,'%Y-%m-%d %H')
        </if>
    </select>

    <!--批量获取点击次数-->
    <select id="getClickCountByServe" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupAppDTO">
        SELECT
        COUNT(1) clickCount,
        serve_id serveId,
        serve_type serveType,
        book_group_id bookGroupId
        FROM
        app_click_record t
        WHERE
        t.book_group_id = #{bookGroupId}
        AND t.serve_id IN
        <foreach collection="serveIds" item="item" open="(" close=")" separator="," index="index">
            #{item}
        </foreach>
        AND t.serve_type=#{serveType}
        GROUP BY
        serve_id,
        serve_type,
        book_group_id
    </select>

    <!--批量获取点击人数-->
    <select id="getClickPeopleCountByServe" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupAppDTO">
        SELECT
        COUNT(tt.wechat_user_id) clickPeopleCount,
        tt.book_group_id bookGroupId,
        tt.serve_id serveId,
        tt.serve_type serveType
        FROM
        (
        SELECT
        t.wechat_user_id,
        t.serve_id,
        t.serve_type,
        t.book_group_id
        FROM
        app_click_record t
        WHERE
        t.book_group_id = #{bookGroupId}
        AND t.serve_id IN
        <foreach collection="serveIds" item="item" open="(" close=")" separator="," index="index">
            #{item}
        </foreach>
        AND t.serve_type=#{serveType}
        GROUP BY
        t.wechat_user_id,
        t.serve_id,
        t.serve_type,
        t.book_group_id
        ) tt
        GROUP BY
        tt.book_group_id,
        tt.serve_id,
        tt.serve_type
    </select>

    <!--根据bookGroupId批量获取点击次数-->
    <select id="getAppClickByBookGroupId" parameterType="map" resultType="com.pcloud.book.group.dto.CountAndTimeDTO">
        SELECT
        DATE_FORMAT(t.create_time, '%Y-%m-%d') date,
        DATE_FORMAT(t.create_time,'%Y-%m-%d %H') time,
        COUNT(1) count
        FROM
        app_click_record t
        WHERE
        t.book_group_id = #{bookGroupId}
        AND DATE_FORMAT(t.create_time, '%Y-%m-%d') IN
        <foreach collection="dateList" index="index" separator="," open="(" close=")" item="item">
            #{item}
        </foreach>
        GROUP BY
        DATE_FORMAT( t.create_time, '%Y-%m-%d %H' )
    </select>

    <!--根据bookGroupIds获取应用点击人数-->
    <select id="getClPeCoByBookGroupIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupIdAndCountDTO">
        SELECT
        tt.book_group_id bookGroupId,
        COUNT(tt.wechat_user_id) count
        FROM
        (
        SELECT
        t.wechat_user_id,
        t.book_group_id
        FROM
        app_click_record t
        WHERE
        t.book_group_id IN
        <foreach collection="bookGroupIds" open="(" separator="," close=")" index="index" item="item">
            #{item}
        </foreach>
        GROUP BY
        t.book_group_id,
        t.wechat_user_id
        ) tt
        GROUP BY
        tt.book_group_id
    </select>

    <!--根据bookGroupIds获取应用点击人数-->
    <select id="getClCoByBookGroupIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupIdAndCountDTO">
        SELECT
        COUNT(1) count,
        t.book_group_id bookGroupId
        FROM
        app_click_record t
        WHERE
        t.book_group_id IN
        <foreach collection="bookGroupIds" open="(" separator="," close=")" index="index" item="item">
            #{item}
        </foreach>
        GROUP BY
        t.book_group_id
    </select>

    <!--根据社群书id和应用或作品id获取各个时间段的点击次数-->
    <select id="getAppClickGroupByTime" parameterType="map" resultType="com.pcloud.book.group.dto.CountAndTimeDTO">
        SELECT
        DATE_FORMAT(t.create_time, '%H') hour,
        COUNT(1) count
        FROM
        app_click_record t
        WHERE
        t.book_group_id = #{bookGroupId}
        <if test="serveId!=null">
            and serve_id=#{serveId}
        </if>
        <if test="serveType!=null">
            and serve_type=#{serveType}
        </if>
        GROUP BY
        DATE_FORMAT(t.create_time, '%H');
    </select>
</mapper>