<?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="ctop.bytedance.ad.advertise.mapper.BytedanceVideoSlogenInfoMapper">
    <select id="selectVideoSlogenReportByAccountIdAndTitle" resultType="java.util.HashMap">

        SELECT
            a.id,
            a.status,
            a.slogan,
            a.project_id AS projectId,
            count(*) AS count,
            sum(d.cost) AS cost,
            sum(d.show_num) AS showNum,
            sum(d.click) AS click,
            sum(d.convert_num) AS convertNum
        FROM
            ctop_bytedance_video_slogen_info a
        LEFT JOIN (
        SELECT
            t1.id,
            t1.title,
            t2.signature,
            t1.account_id
        FROM
        (
        SELECT
            cbc.*
        FROM
            ctop_bytedance_creative cbc
        WHERE
        account_id IN (
        SELECT
        CONCAT(
        ctop_user_allocation.account_id,
        ''
        )
        FROM
        ctop_user_allocation
        WHERE
        1=1
        <if test="projectIds!=null">
            AND project_id IN
            <foreach item="item" index="index" collection="projectIds"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        )
        ) t1
        LEFT JOIN (
        SELECT
        cbvi.*
        FROM
        ctop_bytedance_video_info cbvi
        WHERE
        account_id IN (
        SELECT
        ctop_user_allocation.account_id
        FROM
        ctop_user_allocation
        WHERE
        1=1
        <if test="projectIds!=null">
            AND project_id IN
            <foreach item="item" index="index" collection="projectIds"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        )
        ) t2 ON t1.video_id = t2.vid
        AND t1.account_id = t2.account_id
        LEFT JOIN ctop_user_allocation cua ON cua.account_id = t1.account_id
        WHERE
        1=1
        <if test="projectIds!=null">
            AND cua.project_id IN
            <foreach item="item" index="index" collection="projectIds"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        ) b ON a.slogan = b.title
        AND a.video_code = b.signature
        LEFT JOIN ctop_bytedance_report_creative_daily d ON b.id = d.creative_id
        WHERE
        1=1
        <if test="status!=null">and a.status = #{status}</if>
        <if test="keyWord!=null and keyWord!=''">and a.slogan like concat('%',#{keyWord},'%')</if>
        <if test="startTime!=null and startTime != ''">and STR_TO_DATE(d.stat_datetime,'%Y-%m-%d') &gt;=
            STR_TO_DATE(#{startTime},'%Y-%m-%d')
        </if>
        <if test="endTime!=null and endTime!=''">and STR_TO_DATE(d.stat_datetime,'%Y-%m-%d') &lt;=
            STR_TO_DATE(#{endTime},'%Y-%m-%d')
        </if>
        <if test="projectIds!=null">
            AND a.project_id IN
            <foreach item="item" index="index" collection="projectIds"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        GROUP BY
        a.slogan


    </select>

    <select id="getProjectByUserId" resultType="java.lang.String">
        select
            project.id
        from
            ctop_project_member member left join ctop_project project on project.id = member.project_id
        where
            member.user_id = #{userId}
    </select>


    <select id="getDataBySlogenAndProjectId" resultType="org.jeecg.modules.bytedance.advertise.entity.BytedanceVideoSlogenInfo">
    SELECT
        t1.*,t2.material_name
    FROM
        ctop_bytedance_video_slogen_info t1
    LEFT JOIN ctop_material_info t2 ON t1.video_code = t2.code
    AND t1.project_id = t2.project_id
    WHERE
        slogan =#{slogan} and t1.project_id = #{projectId}
    </select>
</mapper>