<?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.AdvertisingSpaceDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.advertising.entity.AdvertisingSpace">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="ad_name" property="adName" jdbcType="VARCHAR"/>
        <result column="ad_position" property="adPosition" jdbcType="VARCHAR"/>
        <result column="ad_position_mode" property="adPositionMode" jdbcType="VARCHAR"/>
        <result column="ad_position_detail" property="adPositionDetail" jdbcType="VARCHAR"/>
        <result column="master_id" property="masterId" jdbcType="BIGINT"/>
        <result column="ad_link" property="adLink" jdbcType="VARCHAR"/>
        <result column="source_id" property="sourceId" jdbcType="VARCHAR"/>
        <result column="ad_title" property="adTitle" jdbcType="VARCHAR"/>
        <result column="ad_slogan" property="adSlogan" jdbcType="VARCHAR"/>
        <result column="ad_pic" property="adPic" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="is_test" property="isTest" jdbcType="BIT"/>
        <result column="is_open" property="isOpen" jdbcType="BIT"/>
        <result column="ad_detail_mode" property="adDetailMode" jdbcType="VARCHAR" />
        <result column="ad_detail_cover_pic" property="adDetailCoverPic" jdbcType="VARCHAR" />
        <result column="ad_detail_welfare_pic" property="adDetailWelfarePic" jdbcType="VARCHAR" />
        <result column="ad_detail_info" property="adDetailInfo" jdbcType="VARCHAR" />
        <result column="ad_detail_file_id" property="adDetailFileId" jdbcType="VARCHAR" />
        <result column="ad_detail_file_convert_state" property="adDetailFileConvertState" jdbcType="INTEGER" />
        <result column="ad_detail_file_url" property="adDetailFileUrl" jdbcType="VARCHAR" />
        <result column="ad_detail_title" property="adDetailTitle" jdbcType="VARCHAR" />
        <result column="ad_detail_file_pilot_second" property="adDetailFilePilotSecond" jdbcType="BIGINT" />
        <result column="ad_detail_file_pilot_url" property="adDetailFilePilotUrl" jdbcType="VARCHAR" />
        <result column="ad_detail_file_pilot_finish_tip" property="adDetailFilePilotFinishTip" jdbcType="VARCHAR" />
        <result column="ad_form_mobile_check" property="adFormMobileCheck" jdbcType="BIT" />
        <result column="ad_form_remark" property="adFormRemark" jdbcType="VARCHAR" />
        <result column="ad_form_button" property="adFormButton" jdbcType="VARCHAR" />
        <result column="ad_form_link" property="adFormLink" jdbcType="VARCHAR" />
        <result column="ad_detail_file_size" property="adDetailFileSize" jdbcType="BIGINT" />
        <result column="ad_detail_file_name" property="adDetailFileName" jdbcType="VARCHAR" />
        <result column="ad_detail_file_duration" property="adDetailFileDuration" jdbcType="BIGINT" />
        <result column="cpa_id" property="cpaId" jdbcType="BIGINT" />
    </resultMap>

    <resultMap id="AdvertisingSpaceDTO" type="com.pcloud.book.advertising.dto.AdvertisingSpaceDTO">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="ad_name" property="adName" jdbcType="VARCHAR"/>
        <result column="ad_position" property="adPosition" jdbcType="VARCHAR"/>
        <result column="ad_position_mode" property="adPositionMode" jdbcType="VARCHAR"/>
        <result column="ad_position_detail" property="adPositionDetail" jdbcType="VARCHAR"/>
        <result column="master_id" property="masterId" jdbcType="BIGINT"/>
        <result column="ad_link" property="adLink" jdbcType="VARCHAR"/>
        <result column="source_id" property="sourceId" jdbcType="VARCHAR"/>
        <result column="ad_title" property="adTitle" jdbcType="VARCHAR"/>
        <result column="ad_slogan" property="adSlogan" jdbcType="VARCHAR"/>
        <result column="ad_pic" property="adPic" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="is_test" property="isTest" jdbcType="BIT"/>
        <result column="is_open" property="isOpen" jdbcType="BIT"/>
        <result column="ad_detail_mode" property="adDetailMode" jdbcType="VARCHAR" />
        <result column="ad_detail_cover_pic" property="adDetailCoverPic" jdbcType="VARCHAR" />
        <result column="ad_detail_welfare_pic" property="adDetailWelfarePic" jdbcType="VARCHAR" />
        <result column="ad_detail_info" property="adDetailInfo" jdbcType="VARCHAR" />
        <result column="ad_detail_file_id" property="adDetailFileId" jdbcType="VARCHAR" />
        <result column="ad_detail_file_convert_state" property="adDetailFileConvertState" jdbcType="INTEGER" />
        <result column="ad_detail_file_url" property="adDetailFileUrl" jdbcType="VARCHAR" />
        <result column="ad_detail_title" property="adDetailTitle" jdbcType="VARCHAR" />
        <result column="ad_detail_file_pilot_second" property="adDetailFilePilotSecond" jdbcType="BIGINT" />
        <result column="ad_detail_file_pilot_url" property="adDetailFilePilotUrl" jdbcType="VARCHAR" />
        <result column="ad_detail_file_pilot_finish_tip" property="adDetailFilePilotFinishTip" jdbcType="VARCHAR" />
        <result column="ad_form_mobile_check" property="adFormMobileCheck" jdbcType="BIT" />
        <result column="ad_form_remark" property="adFormRemark" jdbcType="VARCHAR" />
        <result column="ad_form_button" property="adFormButton" jdbcType="VARCHAR" />
        <result column="ad_form_link" property="adFormLink" jdbcType="VARCHAR" />
        <result column="ad_detail_file_size" property="adDetailFileSize" jdbcType="BIGINT" />
        <result column="ad_detail_file_name" property="adDetailFileName" jdbcType="VARCHAR" />
        <result column="ad_detail_file_duration" property="adDetailFileDuration" jdbcType="BIGINT" />
        <result column="settlement_method" property="settlementMethod" jdbcType="VARCHAR"/>
        <result column="cpa_id" property="cpaId" jdbcType="BIGINT" />
    </resultMap>

    <sql id="Base_Column_List">
        id, ad_name, ad_position, ad_position_mode, ad_position_detail, master_id, ad_link, source_id,
        ad_title, ad_slogan, ad_pic, create_time, update_time, is_test, is_open, ad_detail_mode,
        ad_detail_cover_pic, ad_detail_welfare_pic, ad_detail_info, ad_detail_file_id, ad_detail_file_convert_state,
        ad_detail_file_url, ad_detail_title, ad_detail_file_pilot_second, ad_detail_file_pilot_url,
        ad_detail_file_pilot_finish_tip, ad_form_mobile_check, ad_form_remark, ad_form_button,
        ad_form_link, ad_detail_file_size, ad_detail_file_name, ad_detail_file_duration,cpa_id
    </sql>

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

    <select id="getDTOById" resultMap="AdvertisingSpaceDTO" parameterType="Long">
        select
        <include refid="Base_Column_List"/>
        from advertising_space
        where id = #{id}
    </select>

    <select id="getDTOByBookId" resultMap="AdvertisingSpaceDTO" parameterType="map">
        SELECT
            s.id,
            s.ad_name,
            s.ad_link,
            s.ad_position,
            s.ad_position_detail,
            s.ad_position_mode,
            s.source_id,
            s.ad_title,
            s.ad_slogan,
            s.ad_pic,
            s.master_id,
            s.ad_detail_mode,
            s.ad_detail_cover_pic,
            s.ad_detail_welfare_pic,
            s.ad_detail_info,
            s.ad_detail_file_id,
            s.ad_detail_file_convert_state,
            s.ad_detail_file_url,
            s.ad_detail_title,
            s.ad_detail_file_pilot_second,
            s.ad_detail_file_pilot_url,
            s.ad_detail_file_pilot_finish_tip,
            s.ad_form_mobile_check,
            s.ad_form_remark,
            s.ad_form_button,
            s.ad_form_link,
            s.ad_detail_file_size,
            s.ad_detail_file_name,
            s.ad_detail_file_duration,
            s.cpa_id
        FROM
            advertising_distribution_book b,
            advertising_space s
        WHERE
            b.ad_id = s.id
        AND s.is_open = 1
        AND s.ad_position_detail = #{adPositionDetail}
        AND b.book_id = #{bookId}
        AND b.adviser_id = #{adviserId}
        AND b.channel_id = #{channelId}
        AND b.is_book_group = 0
        ORDER BY
            b.distribution_time DESC
    </select>

    <select id="getDTOByBookId4BookGroup" resultMap="AdvertisingSpaceDTO" parameterType="map">
        SELECT
            s.id,
            s.ad_name,
            s.ad_link,
            s.ad_position,
            s.ad_position_detail,
            s.ad_position_mode,
            s.source_id,
            s.ad_title,
            s.ad_slogan,
            s.ad_pic,
            s.master_id,
            s.ad_detail_mode,
            s.ad_detail_cover_pic,
            s.ad_detail_welfare_pic,
            s.ad_detail_info,
            s.ad_detail_file_id,
            s.ad_detail_file_convert_state,
            s.ad_detail_file_url,
            s.ad_detail_title,
            s.ad_detail_file_pilot_second,
            s.ad_detail_file_pilot_url,
            s.ad_detail_file_pilot_finish_tip,
            s.ad_form_mobile_check,
            s.ad_form_remark,
            s.ad_form_button,
            s.ad_form_link,
            s.ad_detail_file_size,
            s.ad_detail_file_name,
            s.ad_detail_file_duration,
            s.cpa_id
        FROM
            advertising_distribution_book b,
            advertising_space s
        WHERE
            b.ad_id = s.id
        AND s.is_open = 1
        AND s.ad_position_detail = #{adPositionDetail}
        AND b.book_id = #{bookId}
        AND b.adviser_id = #{adviserId}
        AND b.channel_id = #{channelId}
        AND b.is_book_group = 1
        ORDER BY
            b.distribution_time DESC
        limit 1
    </select>

    <select id="getSpaceNum" resultType="Long">
        select
            count(1)
        from
            advertising_space
    </select>

    <select id="list" parameterType="map" resultMap="AdvertisingSpaceDTO">
        select
        <include refid="Base_Column_List"/>
        from advertising_space
        where 1 = 1
        <if test="name != null">
            AND (ad_name LIKE CONCAT('%', #{name}, '%') OR  source_id LIKE CONCAT(#{name},'%'))
        </if>
        <if test="filterClose == 1">
            AND is_open = 1
        </if>
        <if test="filterTest == 1">
            AND (is_test = 0 OR is_test IS NULL)
        </if>
        <if test="adPosition != null">
            AND ad_position = #{adPosition}
        </if>
        <if test="type != null and type == 'WECHAT_GROUP'.toString()">
            AND ad_position = 'WECHAT_GROUP_MSG'
        </if>
        <if test="type != null and type == 'BOOK'.toString()">
            AND ad_position != 'WECHAT_GROUP_MSG' OR ad_position IS NULL
        </if>
        order by
            create_time desc
    </select>

    <select id="advertisingDetail4Book" parameterType="map" resultMap="AdvertisingSpaceDTO">
        SELECT
            s.id,
            s.ad_name,
            s.ad_position,
            s.ad_position_detail,
            s.ad_position_mode,
            s.master_id,
            s.ad_pic,
            s.is_open,
            b.distribution_time distributionTime
        FROM
            advertising_distribution_book b,
            advertising_space s
        WHERE
            b.ad_id = s.id
        AND b.book_id = #{bookId}
        AND b.channel_id = #{channelId}
        AND b.adviser_id = #{adviserId}
        AND s.ad_position != 'WECHAT_GROUP_MSG'
        ORDER BY
            b.distribution_time DESC
    </select>

    <select id="advertisingDetail4WechatGroup" parameterType="map" resultMap="AdvertisingSpaceDTO">
        SELECT
            s.id,
            s.ad_name,
            s.ad_position,
            s.ad_position_detail,
            s.ad_position_mode,
            s.master_id,
            s.ad_pic,
            s.ad_link,
            s.is_open,
            MAX(b.distribution_time) distributionTime,
            COUNT(1) distributionNum
        FROM
            advertising_distribution_book b,
            advertising_space s
        WHERE
            b.ad_id = s.id
        AND b.qrcode_id = #{qrcodeId}
        AND s.ad_position = 'WECHAT_GROUP_MSG'
        GROUP BY
            s.id
        ORDER BY
            b.distribution_time DESC
    </select>

    <select id="advertisingDetail4Master" parameterType="map" resultMap="AdvertisingSpaceDTO">
        SELECT
        s.id,
        s.ad_name,
        s.ad_position,
        s.ad_position_detail,
        s.ad_position_mode,
        s.master_id,
        s.source_id,
        s.ad_pic,
        s.is_open,
        b.distribution_time distributionTime,
        b.book_id bookId,
        b.qrcode_id qrcodeId,
        b.agent_id agentId,
        b.adviser_id adviserId,
        b.channel_id channelId,
        m.settlement_method
        FROM
        advertising_space s
        LEFT JOIN advertising_distribution_book b ON b.ad_id = s.id
        LEFT JOIN advertising_settlement_method m ON s.id = m.ad_id
        AND s.master_id = m.master_id
        WHERE
            s.master_id = #{masterId}
        GROUP BY
            s.id,
            b.book_id,
            b.channel_id,
            b.classify_id,
            b.qrcode_id
        ORDER BY
            b.distribution_time DESC
    </select>

    <delete id="deleteById" parameterType="Long">
        delete from advertising_space
        where id = #{id}
    </delete>

    <insert id="insert" parameterType="com.pcloud.book.advertising.entity.AdvertisingSpace"
            useGeneratedKeys="true" keyProperty="id">
        insert into advertising_space
        <trim prefix="(" suffix=")" suffixOverrides=",">
            ad_name,
            ad_position,
            ad_position_mode,
            ad_position_detail,
            master_id,
            ad_link,
            source_id,
            ad_title,
            ad_slogan,
            ad_pic,
            create_time,
            update_time,
            is_open,
            ad_detail_mode,
            ad_detail_cover_pic, ad_detail_welfare_pic, ad_detail_info,
            ad_detail_file_id, ad_detail_file_convert_state,
            ad_detail_file_url, ad_detail_title, ad_detail_file_pilot_second,
            ad_detail_file_pilot_url, ad_detail_file_pilot_finish_tip,
            ad_form_mobile_check, ad_form_remark, ad_form_button,
            ad_form_link,ad_detail_file_size, ad_detail_file_name,
            ad_detail_file_duration,
            cpa_id
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{adName,jdbcType=VARCHAR},
            #{adPosition,jdbcType=VARCHAR},
            #{adPositionMode,jdbcType=VARCHAR},
            #{adPositionDetail,jdbcType=VARCHAR},
            #{masterId,jdbcType=BIGINT},
            #{adLink,jdbcType=VARCHAR},
            #{sourceId,jdbcType=VARCHAR},
            #{adTitle,jdbcType=VARCHAR},
            #{adSlogan,jdbcType=VARCHAR},
            #{adPic,jdbcType=VARCHAR},
            NOW(),
            NOW(),
            #{isOpen,jdbcType=BIT},
            #{adDetailMode,jdbcType=VARCHAR},
            #{adDetailCoverPic,jdbcType=VARCHAR}, #{adDetailWelfarePic,jdbcType=VARCHAR}, #{adDetailInfo,jdbcType=VARCHAR},
            #{adDetailFileId,jdbcType=VARCHAR}, #{adDetailFileConvertState,jdbcType=INTEGER},
            #{adDetailFileUrl,jdbcType=VARCHAR}, #{adDetailTitle,jdbcType=VARCHAR}, #{adDetailFilePilotSecond,jdbcType=BIGINT},
            #{adDetailFilePilotUrl,jdbcType=VARCHAR}, #{adDetailFilePilotFinishTip,jdbcType=VARCHAR},
            #{adFormMobileCheck,jdbcType=BIT}, #{adFormRemark,jdbcType=VARCHAR}, #{adFormButton,jdbcType=VARCHAR},
            #{adFormLink,jdbcType=VARCHAR}, #{adDetailFileSize}, #{adDetailFileName},
            #{adDetailFileDuration},
            #{cpaId}
        </trim>
    </insert>

    <update id="update" parameterType="com.pcloud.book.advertising.entity.AdvertisingSpace">
        update advertising_space
        <set>
            <if test="adName != null">
                ad_name = #{adName,jdbcType=VARCHAR},
            </if>
            <if test="adPosition != null">
                ad_position = #{adPosition,jdbcType=VARCHAR},
            </if>
            <if test="adPositionMode != null">
                ad_position_mode = #{adPositionMode,jdbcType=VARCHAR},
            </if>
            <if test="adPositionDetail != null">
                ad_position_detail = #{adPositionDetail,jdbcType=VARCHAR},
            </if>
            <if test="masterId != null">
                master_id = #{masterId,jdbcType=BIGINT},
            </if>
            <if test="adLink != null">
                ad_link = #{adLink,jdbcType=VARCHAR},
            </if>
            <if test="sourceId != null">
                source_id = #{sourceId,jdbcType=VARCHAR},
            </if>
            <if test="adTitle != null">
                ad_title = #{adTitle,jdbcType=VARCHAR},
            </if>
            <if test="adSlogan != null">
                ad_slogan = #{adSlogan,jdbcType=VARCHAR},
            </if>
            <if test="adPic != null">
                ad_pic = #{adPic,jdbcType=VARCHAR},
            </if>
                update_time = NOW(),
            <if test="isOpen != null">
                is_open = #{isOpen,jdbcType=BIT},
            </if>
            <if test="adDetailMode != null" >
                ad_detail_mode = #{adDetailMode,jdbcType=VARCHAR},
            </if>
            <if test="adDetailCoverPic != null" >
                ad_detail_cover_pic = #{adDetailCoverPic,jdbcType=VARCHAR},
            </if>
            <if test="adDetailWelfarePic != null" >
                ad_detail_welfare_pic = #{adDetailWelfarePic,jdbcType=VARCHAR},
            </if>
            <if test="adDetailInfo != null" >
                ad_detail_info = #{adDetailInfo,jdbcType=VARCHAR},
            </if>
            <if test="adDetailFileId != null" >
                ad_detail_file_id = #{adDetailFileId,jdbcType=VARCHAR},
            </if>
            <if test="adDetailFileConvertState != null" >
                ad_detail_file_convert_state = #{adDetailFileConvertState,jdbcType=INTEGER},
            </if>
            <if test="adDetailFileUrl != null" >
                ad_detail_file_url = #{adDetailFileUrl,jdbcType=VARCHAR},
            </if>
            <if test="adDetailTitle != null" >
                ad_detail_title = #{adDetailTitle,jdbcType=VARCHAR},
            </if>
            <if test="adDetailFilePilotSecond != null" >
                ad_detail_file_pilot_second = #{adDetailFilePilotSecond,jdbcType=BIGINT},
            </if>
            <if test="adDetailFilePilotUrl != null" >
                ad_detail_file_pilot_url = #{adDetailFilePilotUrl,jdbcType=VARCHAR},
            </if>
            <if test="adDetailFilePilotFinishTip != null" >
                ad_detail_file_pilot_finish_tip = #{adDetailFilePilotFinishTip,jdbcType=VARCHAR},
            </if>
            <if test="adFormMobileCheck != null" >
                ad_form_mobile_check = #{adFormMobileCheck,jdbcType=BIT},
            </if>
            <if test="adFormRemark != null" >
                ad_form_remark = #{adFormRemark,jdbcType=VARCHAR},
            </if>
            <if test="adFormButton != null" >
                ad_form_button = #{adFormButton,jdbcType=VARCHAR},
            </if>
            <if test="adFormLink != null" >
                ad_form_link = #{adFormLink,jdbcType=VARCHAR},
            </if>
            <if test="adDetailFileSize != null">
                ad_detail_file_size = #{adDetailFileSize},
            </if>
            <if test="adDetailFileName != null">
                ad_detail_file_name = #{adDetailFileName},
            </if>
            <if test="adDetailFileDuration != null">
                ad_detail_file_duration = #{adDetailFileDuration},
            </if>
            <if test="cpaId != null">
                cpa_id = #{cpaId},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

    <select id="getUnConvertFile" resultMap="BaseResultMap" parameterType="String">
        select <include refid="Base_Column_List"></include>
        from advertising_space
        where  ad_detail_file_id = #{adDetailFileId,jdbcType=VARCHAR}
    </select>

    <update id="updateByFileId" parameterType="com.pcloud.book.advertising.entity.AdvertisingSpace">
        update advertising_space
        <set>
            <if test="adDetailFileConvertState != null" >
                ad_detail_file_convert_state = #{adDetailFileConvertState,jdbcType=INTEGER},
            </if>
            <if test="adDetailFileUrl != null" >
                ad_detail_file_url = #{adDetailFileUrl,jdbcType=VARCHAR},
            </if>
            <if test="adDetailFilePilotUrl != null" >
                ad_detail_file_pilot_url = #{adDetailFilePilotUrl,jdbcType=VARCHAR},
            </if>
        </set>
        where ad_detail_file_id = #{adDetailFileId,jdbcType=VARCHAR}
    </update>

    <update id="updateTestStatus" parameterType="map">
        UPDATE advertising_space
        SET is_test = #{isTest}
        WHERE
        id = #{id}
    </update>

    <!--根据id集合获取-->
    <select id="getByIds" parameterType="map" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/> from advertising_space
        where id in
        <foreach collection="ids" item="item" open="(" separator="," close=")">
            ${item}
        </foreach>
    </select>

    <select id="getAdIdsByMasterId" resultType="Long" parameterType="Long">
        SELECT
        id
        FROM
        advertising_space
        WHERE
        master_id = #{masterId}
    </select>

    <select id="getAdIdsByCPAId" resultType="long" parameterType="long">
        select
        id
        from
        advertising_space
        WHERE
        cpa_id = #{cpaId,jdbcType=BIGINT}
    </select>

    <update id="updateCPAId" parameterType="map">
        UPDATE advertising_space
        SET cpa_id = #{cpaId,jdbcType=BIGINT}
        WHERE
        id = #{id}
    </update>

    <select id="getCPASpaceList" resultMap="BaseResultMap">
        SELECT
        s.*
        FROM
        advertising_space s
        LEFT JOIN advertising_settlement_method m ON s.id = m.ad_id
        WHERE
        m.settlement_method = 'CPA' AND cpa_id IS NULL
    </select>

</mapper>