<?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.AppletThirdResourcesDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.applet.entity.AppletThirdResources">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="resource_number" property="number" jdbcType="VARCHAR"/>
        <result column="resource_type" property="type" jdbcType="TINYINT"/>
        <result column="resource_name" property="name" jdbcType="VARCHAR"/>
        <result column="app_Id" property="appletsId" jdbcType="VARCHAR"/>
        <result column="resource_source" property="source" jdbcType="VARCHAR"/>
        <result column="resource_img_url" property="resourceImgUrl" jdbcType="VARCHAR"/>
        <result column="resource_url" property="url" jdbcType="VARCHAR"/>
        <result column="remark" property="remark" jdbcType="VARCHAR"/>
        <result column="creator" property="creator" jdbcType="BIGINT"/>
        <result column="updator" property="updator" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="route_code" property="routeCode" jdbcType="TINYINT"/>
        <result column="guide" property="guide" jdbcType="VARCHAR"/>
        <result column="top_img_url" property="topImgUrl" jdbcType="VARCHAR"/>
        <result column="float_img_url" property="floatImgUrl" jdbcType="VARCHAR"/>
        <result column="button_img_url" property="buttonImgUrl" jdbcType="VARCHAR"/>
        <result column="show_type" property="showType" jdbcType="TINYINT"/>
        <result column="base_app_id" property="baseAppId" jdbcType="VARCHAR"/>
        <result column="big_img_url" property="bigImgUrl" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
    id,resource_number,resource_type,resource_name,app_Id,resource_source,resource_img_url,resource_url,remark,create_time,update_time,
    creator,updator,route_code,guide,top_img_url,float_img_url,button_img_url,show_type,base_app_id,big_img_url
  </sql>


    <select id="getConfigPoint" parameterType="map" resultType="com.pcloud.book.applet.dto.AppletConfigPointDTO">
        SELECT
            ralation.third_resources_id thirdResourcesId,
            GROUP_CONCAT(news.url_number) newsNumber
        FROM
            applet_third_resources_relation ralation
        LEFT JOIN applet_news news ON ralation.from_id = news.id
        WHERE ralation.third_resources_id  in
        <foreach collection="list" item="item" index="index" separator="," close=")" open="(">
            #{item}
        </foreach>
    </select>


    <select id="exitThirResources" parameterType="map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from applet_third_resources
        where 1=1
        and resource_number = #{resourceNumber}
        order by update_time desc
        limit 1
    </select>


    <select id="getThirdResources" parameterType="map" resultType="com.pcloud.book.applet.entity.AppletThirdResources">
        select
            a.id AS id,
            a.resource_number AS number,
            a.resource_type AS type,
            a.resource_name AS name,
            a.app_Id AS appletsId,
            a.resource_source AS source,
            a.resource_img_url AS resourceImgUrl,
            a.big_img_url as bigImgUrl,
            a.resource_url AS url,
            a.remark AS remark,
            a.creator,
            a.updator,
            a.create_time AS createTime,
            a.update_time AS updateTime,
            a.route_code AS routeCode,
            a.guide AS guide,
            b.material_url AS materialUrl,
            a.top_img_url topImgUrl,
            a.float_img_url floatImgUrl,
            a.button_img_url buttonImgUrl,
            a.show_type showType,
            a.base_app_id baseAppId
        from applet_third_resources a
        left join biz_material b on a.id = b.biz_id and b.biz_type = 1
        where 1=1
        <if test="type!=null">
            and a.resource_type=#{type}
        </if>
        <if test="source!=null">
            and a.resource_source=#{source}
        </if>
        <if test="keyValue!=null">
            and (a.resource_number LIKE concat('%',#{keyValue},'%') OR a.resource_name LIKE concat('%',#{keyValue},'%'))
        </if>
        order by a.update_time desc
    </select>

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

    <select id="getByResourcesId" parameterType="map" resultType="com.pcloud.book.applet.entity.AppletThirdResources">
         select
            a.id AS id,
            a.resource_number AS number,
            a.resource_type AS type,
            a.resource_name AS name,
            a.app_Id AS appletsId,
            a.resource_source AS source,
            a.resource_img_url AS resourceImgUrl,
            a.resource_url AS url,
            a.remark AS remark,
            a.creator,
            a.updator,
            a.create_time AS createTime,
            a.update_time AS updateTime,
            a.route_code AS routeCode,
            a.guide AS guide,
            b.material_url AS materialUrl,
            a.top_img_url topImgUrl,
            a.float_img_url floatImgUrl,
            a.button_img_url buttonImgUrl,
            a.show_type showType,
            a.resource_url AS appletsUrl,
            a.base_app_id baseAppId
        from applet_third_resources a
        left join biz_material b on a.id = b.biz_id and b.biz_type = 1
        where a.id=#{id}
    </select>

    <select id="getAppletsByIds" parameterType="map" resultType="com.pcloud.book.applet.entity.AppletThirdResources">
        select
        a.id AS id,
        a.resource_number AS number,
        a.resource_type AS type,
        a.resource_name AS name,
        a.app_Id AS appletsId,
        a.resource_source AS source,
        a.resource_img_url AS resourceImgUrl,
        a.big_img_url AS bigImgUrl,
        a.resource_url AS url,
        a.remark AS remark,
        a.creator,
        a.updator,
        a.create_time AS createTime,
        a.update_time AS updateTime,
        a.route_code AS routeCode,
        a.guide AS guide,
        b.material_url AS materialUrl,
        a.top_img_url topImgUrl,
        a.float_img_url floatImgUrl,
        a.button_img_url buttonImgUrl,
        a.show_type showType,
        a.base_app_id baseAppId
        from applet_third_resources a
        left join biz_material b on a.id = b.biz_id and b.biz_type = 1
        where a.id in
        <foreach collection="ids" item="item" index="index" separator="," close=")" open="(">
            #{item}
        </foreach>
    </select>

    <select id="getThirdResourcesByNewsId" parameterType="Long" resultType="com.pcloud.book.applet.entity.AppletThirdResources">
        SELECT
            relation.from_id fromId,
            third.id,
            third.app_Id appletsId,
            third.resource_name name,
            third.resource_number number,
            third.resource_type type,
            third.resource_img_url resourceImgUrl,
            third.big_img_url bigImgUrl,
            third.resource_url url,
            bm.material_url materialUrl,
            third.route_code routeCode,
            third.guide,
            third.resource_source source,
            third.guide,
            third.top_img_url topImgUrl,
            third.float_img_url floatImgUrl,
            third.button_img_url buttonImgUrl,
            third.show_type showType,
            third.base_app_id baseAppId
        FROM
            applet_third_resources_relation relation
        right JOIN applet_third_resources third ON relation.third_resources_id = third.id
        LEFT JOIN biz_material bm ON third.id = bm.biz_id AND bm.biz_type = 1
        where relation.from_id = #{newsId}
    </select>



    <select id="getListThirdResourcesAnalysis" parameterType="map"
            resultType="com.pcloud.book.applet.dto.AppletThirdResourcesDTO">
        SELECT  * from(
        SELECT
        n.id,
        n.resource_number number,
        n.resource_type type,
        n.resource_name name,
        n.app_Id appletsId,
        n.resource_source source,
        n.resource_img_url resourceImgUrl,
        n.resource_url url,
        n.remark,
        n.create_time createTime,
        n.update_time updateTime,
        n.creator,
        n.updator,
        GROUP_CONCAT(case when news.id != ' ' THEN news.id else null END) newsIds,
        GROUP_CONCAT(case when news.url_number != ' ' THEN news.url_number else null END) newsNumber,
        n.base_app_id baseAppId
        FROM
        applet_third_resources n
        LEFT JOIN applet_third_resources_relation ralation ON n.id = ralation.third_resources_id
        LEFT JOIN applet_news news ON ralation.from_id = news.id
        WHERE
        1 = 1
        <if test="source != null">
            AND n.resource_source = #{source}
        </if>
        <if test="resourcesType != null">
            AND n.resource_type = #{resourcesType}
        </if>
        GROUP BY
        n.id
        ORDER BY
        n.update_time DESC
        )result where   1 = 1
        <if test="keyValue != null">
            AND (result.number LIKE CONCAT("%",#{keyValue},"%") or result.name LIKE CONCAT("%",#{keyValue},"%") or result.newsNumber LIKE CONCAT("%",#{keyValue},"%"))
        </if>
    </select>


    <insert id="insert" parameterType="com.pcloud.book.applet.entity.AppletThirdResources" useGeneratedKeys="true"
            keyProperty="id">
    insert into applet_third_resources ( resource_number,resource_type,resource_name,
        app_Id,resource_source,resource_img_url,
        resource_url,remark,create_time,
        update_time,creator,updator,route_code,guide, top_img_url, float_img_url, button_img_url, show_type,base_app_id,big_img_url
    )
    values (#{number,jdbcType=VARCHAR}, #{type,jdbcType=TINYINT}, #{name,jdbcType=VARCHAR},
       #{appletsId,jdbcType=VARCHAR}, #{source,jdbcType=VARCHAR}, #{resourceImgUrl,jdbcType=VARCHAR},
       #{url,jdbcType=VARCHAR}, #{remark,jdbcType=VARCHAR}, now(),
       now(), #{creator,jdbcType=BIGINT}, #{updator,jdbcType=BIGINT},#{routeCode,jdbcType=TINYINT}, #{guide,jdbcType=VARCHAR},
       #{topImgUrl}, #{floatImgUrl}, #{buttonImgUrl}, #{showType}, #{baseAppId}, #{bigImgUrl}
    )
  </insert>

    <update id="update" parameterType="com.pcloud.book.applet.entity.AppletThirdResources">
        update applet_third_resources
        <set>
            <if test="number != null">
                resource_number = #{number,jdbcType=VARCHAR},
            </if>
            <if test="type != null">
                resource_type = #{type,jdbcType=TINYINT},
            </if>
            <if test="name != null">
                resource_name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="appletsId != null">
                app_Id = #{appletsId,jdbcType=VARCHAR},
            </if>
            <if test="source != null">
                resource_source = #{source,jdbcType=VARCHAR},
            </if>
            <if test="resourceImgUrl != null">
                resource_img_url = #{resourceImgUrl,jdbcType=VARCHAR},
            </if>
                big_img_url = #{bigImgUrl,jdbcType=VARCHAR},
            <if test="url != null">
                resource_url = #{url,jdbcType=VARCHAR},
            </if>
            <if test="remark != null">
                remark = #{remark,jdbcType=VARCHAR},
            </if>
            <if test="routeCode != null">
                route_code = #{routeCode,jdbcType=TINYINT},
            </if>
            <if test="guide != null">
                guide = #{guide,jdbcType=VARCHAR},
            </if>
            <if test="topImgUrl != null">
                top_img_url = #{topImgUrl},
            </if>
            <if test="floatImgUrl != null">
                float_img_url = #{floatImgUrl},
            </if>
            <if test="buttonImgUrl != null">
                button_img_url = #{buttonImgUrl},
            </if>
            <if test="showType != null">
                show_type = #{showType},
            </if>
            update_time = NOW(),
            <if test="baseAppId != null">
                base_app_id = #{baseAppId},
            </if>
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <delete id="deleteById">
    delete from applet_third_resources where id=#{id}
  </delete>

    <select id="listResourceIdByGradeLabelAndClickCount" parameterType="map" resultType="long">
        SELECT
        a.target_id
        FROM
        applet_resource_apply a
        INNER JOIN applet_third_resources r ON a.target_id = r.id
        LEFT JOIN applet_thirdresources_click_record c ON a.target_id = c.third_resources_id
        AND c.create_time > #{startDate}
        WHERE
        a.crowd_type = #{crowdType}
        AND a.target_type = "THIRD_RESOURCE"
        AND a.target_id > 0
        GROUP BY
        a.target_id
        ORDER BY
        COUNT(c.id) DESC,
        c.create_time DESC,
        a.target_id DESC
    </select>

    <select id="countResourceIdByGradeLabel" parameterType="map" resultType="integer">
  	    SELECT
		 COUNT(DISTINCT	a.target_id)
		FROM
			applet_resource_apply a
		INNER JOIN applet_third_resources r ON a.target_id = r.id
		WHERE
			a.crowd_type = #{crowdType}
		AND a.target_type = "THIRD_RESOURCE"
		AND a.target_id > 0
    </select>


</mapper>