<?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.pcloud.book.applet.dao.impl.AppletThirdResourcesClickDaoImpl">

    <resultMap type="com.pcloud.book.applet.entity.AppletThirdResourcesClick" id="AppletLinkClickMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="wechatUserId" column="wechat_user_id" jdbcType="INTEGER"/>
        <result property="thirdResourcesId" column="third_resources_id" jdbcType="INTEGER"/>
        <result property="thirdResourcesName" column="third_resources_name" jdbcType="VARCHAR"/>
        <result property="link" column="link" jdbcType="VARCHAR"/>
        <result property="recordType" column="record_type" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="officialAccountsId" column="official_accounts_id" jdbcType="INTEGER"/>
    </resultMap>

    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into
         applet_thirdresources_click_record(
         wechat_user_id,
         third_resources_id,
         third_resources_name,
         link,record_type,
         create_time,
         update_time,
         book_id,
         adviser_id,
         channel_id,
         page,
         rights_setting_classify_id,
         news_id,
         gift_package_id,
         official_accounts_id
         )
        values (
            #{wechatUserId},
            #{thirdResourcesId},
            #{thirdResourcesName},
            #{link},
            #{recordType},
            now(),
            now(),
            #{bookId},
            #{adviserId},
            #{channelId},
            #{page},
            #{rightsSettingClassifyId},
            #{newsId},
            #{giftPackageId},
            #{officialAccountsId}
            )
    </insert>

    <select id="mapPVUV" parameterType="map" resultType="com.pcloud.book.applet.dto.PvuvDTO">
        SELECT
        third_resources_id fromId,
        COUNT(1) pv,
        COUNT(DISTINCT wechat_user_id) uv
        FROM applet_thirdresources_click_record
        WHERE 1=1
        AND record_type = #{recordType}
        AND third_resources_id in
        <foreach collection="list" item="item" index="index" separator="," close=")" open="(">
            #{item}
        </foreach>
        <if test="intervalDaya != null">
            AND date_format(create_time ,'%Y-%m-%d') between DATE_SUB(CURDATE(),INTERVAL #{intervalDaya} DAY) and DATE_SUB(CURDATE(),INTERVAL 1
            DAY)
        </if>
        GROUP BY third_resources_id
    </select>

    <select id="mapPVUVByMonth" parameterType="map" resultType="com.pcloud.book.applet.dto.AppletChartDateDTO">
  SELECT b.createDate,IFNULL(aa.pv,0) pv,IFNULL(aa.uv,0) uv from
		(SELECT
		DATE_FORMAT(create_time,'%Y-%m-%d') createDate,
        COUNT(1) pv,
        COUNT(DISTINCT wechat_user_id) uv
        FROM applet_thirdresources_click_record
        WHERE 1=1
        AND record_type =  #{recordType}
		and third_resources_id=  #{thirdResourcesId}
        AND create_time between date_add(CURDATE(), interval -1 month) and DATE_SUB(CURDATE(),INTERVAL -1 DAY)
        GROUP BY third_resources_id,DATE_FORMAT(create_time,'%Y-%m-%d')
		) aa
		RIGHT JOIN
			(select a.Date as createDate from
				(select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date from
					(select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
			  cross join
					(select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
				cross join
					(select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
				) a
			where a.Date between DATE_SUB(CURDATE(), INTERVAL 30 DAY) and DATE_SUB(CURDATE(),INTERVAL 1 DAY))b ON b.createDate = aa.createDate
			ORDER BY b.createDate asc
    </select>
    
    
    <select id="listThirdResourceRecord" parameterType="map" resultType="com.pcloud.book.applet.dto.ThirdResourceRecordDTO">
        select
        a.id,
        a.wechat_user_id wechatUserId,
        a.third_resources_id thirdResourceId,
        a.record_type recordType,
        a.create_time createTime,
        b.url_number urlNumber,
        a.news_id newsId,
        a.booK_id bookId,
        a.adviser_id adviserId,
        c.book_name bookName,
        c.isbn,
        a.gift_package_id giftPackageId
        from
        applet_thirdresources_click_record a
        left join
        applet_news b on a.news_id = b.id
        left join
        book c on a.book_id = c.BOOK_ID
        <where>
        a.book_id is not null and a.third_resources_id = #{thirdResourceId}
            <if test="queryName  != null">
                and (
                c.book_name like concat("%",#{queryName},"%")
                or
                c.isbn like concat("%",#{queryName},"%")
                )
            </if>
            <if test="wechatUserIds != null">
                and
                a.wechat_user_id in
                <foreach collection="wechatUserIds" item="item" index="index" open="(" close=")" separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="recordType != null">
                and a.record_type = #{recordType}
            </if>
        </where>
        order by a.create_time desc

    </select>

</mapper>
