<?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.moka.gdtauto.mapper.AdPlanCreativeMapper">

    <!-- 批量更新广告计划 system_status，依赖 allowMultiQueries=true -->
    <update id="batchUpdateApprovalStatus" parameterType="java.util.List">
        <foreach collection="creatives" item="item" separator=";">
            UPDATE ad_plan_creative
            SET 
                configured_status = #{item.configuredStatus},
                approval_status = #{item.approvalStatus},
                system_delete = #{item.systemDelete},
                update_time   = NOW()
            WHERE creative_id = #{item.creativeId}
        </foreach>
    </update>

     <!-- 批量更新广告计划 REQUEST_TIME 口径日报数据 -->
    <update id="batchUpdateReqDailyReport" parameterType="java.util.List">
        <foreach collection="creatives" item="item" separator=";">
            UPDATE ad_plan_creative
            SET cost              = #{item.cost},
                view_count        = #{item.viewCount},
                valid_click_count = #{item.validClickCount},
                ctr               = #{item.ctr},
                conversions_cost  = #{item.conversionsCost},
                req_income_val1  = #{item.reqIncomeVal1},
                req_income_roi1  = #{item.reqIncomeRoi1},
                scan_follow_user_cost = #{item.scanFollowUserCost},
                update_time       = NOW()
            WHERE creative_id = #{item.creativeId}
        </foreach>
    </update>

    <!-- 批量更新广告计划 REPORTING_TIME 口径日报数据 -->
    <update id="batchUpdateRepDailyReport" parameterType="java.util.List">
        <foreach collection="creatives" item="item" separator=";">
            UPDATE ad_plan_creative
            SET rep_income_val1 = #{item.repIncomeVal1},
                rep_income_roi1 = #{item.repIncomeRoi1},
                update_time      = NOW()
            WHERE creative_id = #{item.creativeId}
        </foreach>
    </update>

    <!-- md5ApprovalCount --> 

    <update id="md5ApprovalCount">
         update oss_file f inner join (
                select creative_file_md5, approval_status,
                sum(case when approval_status = 'CREATIVE_SET_APPROVAL_STATUS_NORMAL' then 1 else 0 end)  as nomarl_cnt,
                sum(case when approval_status = 'CREATIVE_SET_APPROVAL_STATUS_DENIED' then 1 else 0 end) as denied_cnt
                from ad_plan_creative
                where creative_file_md5 is not null and approval_status != ''
                group by creative_file_md5, approval_status ) t 
        set f.normal_cnt = t.nomarl_cnt, f.denied_cnt = t.denied_cnt
        where f.file_md5 = t.creative_file_md5
    </update>

    <!-- listAccountId --> 

    <select id="listAccountId">
        select distinct account_id from ad_plan_creative
        <where>
            <if test="confId != null">
               ad_plan_conf_id = #{confId}
            </if>  
        </where>
         limit #{start}, #{limit}
    </select>

    <!-- updateReqDailyReport --> 

    <update id="updateReqDailyReportConfId">
        update req_daily_creative_report r
        inner join (select p.ad_plan_conf_id, p.adgroup_id, c.account_group_id from ad_plan p
            left join ad_plan_conf c on p.ad_plan_conf_id = c.id ) t on r.adgroup_id = t.adgroup_id
        set r.conf_id = t.ad_plan_conf_id, r.group_id = t.account_group_id
        where r.date  between #{startDate} and #{endDate};
    </update> 

    <!-- updateRepDailyReportConfId --> 

    <update id="updateRepDailyReportConfId">
        update rep_daily_creative_report r
            inner join (select p.ad_plan_conf_id, p.adgroup_id, c.account_group_id from ad_plan p
                left join ad_plan_conf c on p.ad_plan_conf_id = c.id ) t on r.adgroup_id = t.adgroup_id
            set r.conf_id = t.ad_plan_conf_id, r.group_id = t.account_group_id
            where r.date  between #{startDate} and #{endDate};
    </update>


     </mapper>
