<?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="cn.com.ctop.kuaishou.modules.batch.mapper.KuaiShouReportDailyMaterialMapper">
    <insert id="batchReplace">
        replace into ctop_kuaishou_report_daily_material(
        account_id,
        photo_id,
        channel_type,
        signature,
        image_token,
        cover_url,
        photo_url,
        creative_material_type_string,
        ad_scene,
        charge,
        photo_show,
        aclick,
        bclick,
        photo_share,
        photo_comment,
        photo_like,
        follow,
        cancel_follow,
        report,
        block,
        negative,
        download_started,
        download_completed,
        activation,
        submit,
        stat_date,
        photo_click,
        photo_click_ratio,
        action_ratio,
        impression_1k_cost,
        photo_click_cost,
        action_cost,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay_first_day_roi,
        event_pay,
        event_pay_purchase_amount,
        event_pay_roi,
        event_register,
        event_register_cost,
        event_register_ratio,
        event_jin_jian_app,
        event_jin_jian_app_cost,
        event_credit_grant_app,
        event_credit_grant_app_cost,
        event_credit_grant_app_ratio,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_jin_jian_landing_page,
        event_jin_jian_landing_page_cost,
        event_credit_grant_landing_page,
        event_credit_grant_landing_page_cost,
        event_credit_grant_landing_ratio,
        event_next_day_stay_cost,
        event_next_day_stay_ratio,
        form_action_ratio,
        event_order_paid_cost,
        event_next_day_stay,
        form_cost,
        form_count,
        play_3s_ratio
        )
        values
        <foreach collection="addList" item="material" separator=",">
            (
            #{material.accountId},
            #{material.photoId},
            #{material.channelType},
            #{material.signature},
            #{material.imageToken},
            #{material.coverUrl},
            #{material.photoUrl},
            #{material.creativeMaterialTypeString},
            #{material.adScene},
            #{material.charge},
            #{material.photoShow},
            #{material.aclick},
            #{material.bclick},
            #{material.photoShare},
            #{material.photoComment},
            #{material.photoLike},
            #{material.follow},
            #{material.cancelFollow},
            #{material.report},
            #{material.block},
            #{material.negative},
            #{material.downloadStarted},
            #{material.downloadCompleted},
            #{material.activation},
            #{material.submit},
            #{material.statDate},
            #{material.photoClick},
            #{material.photoClickRatio},
            #{material.actionRatio},
            #{material.impression1kCost},
            #{material.photoClickCost},
            #{material.actionCost},
            #{material.eventPayFirstDay},
            #{material.eventPayPurchaseAmountFirstDay},
            #{material.eventPayFirstDayRoi},
            #{material.eventPay},
            #{material.eventPayPurchaseAmount},
            #{material.eventPayRoi},
            #{material.eventRegister},
            #{material.eventRegisterCost},
            #{material.eventRegisterRatio},
            #{material.eventJinJianApp},
            #{material.eventJinJianAppCost},
            #{material.eventCreditGrantApp},
            #{material.eventCreditGrantAppCost},
            #{material.eventCreditGrantAppRatio},
            #{material.eventOrderPaid},
            #{material.eventOrderPaidPurchaseAmount},
            #{material.eventJinJianLandingPage},
            #{material.eventJinJianLandingPageCost},
            #{material.eventCreditGrantLandingPage},
            #{material.eventCreditGrantLandingPageCost},
            #{material.eventCreditGrantLandingRatio},
            #{material.eventNextDayStayCost},
            #{material.eventNextDayStayRatio},
            #{material.formActionRatio},
            #{material.eventOrderPaidCost},
            #{material.eventNextDayStay},
            #{material.formCost},
            #{material.formCount},
            #{material.play3sRatio}
            )
        </foreach>

    </insert>


    <insert id="loadMaterialDailyReport">
        LOAD
        DATA local INFILE
        #{localPath}
        INTO
        TABLE
        ctop_kuaishou_report_daily_material
        FIELDS
        TERMINATED
        BY
        ','
        enclosed
        by
        '"'
        LINES
        TERMINATED
        BY
        '\n'
        IGNORE
        1
        LINES
        (
        photo_id,
        image_token,
        cover_url,
        photo_url,
        @
        photo_caption,
        @
        creative_material_type_string,
        @
        ad_scene,
        charge,
        photo_show,
        aclick,
        bclick,
        photo_share,
        photo_comment,
        photo_like,
        follow,
        cancel_follow,
        report,
        block,
        negative,
        download_started,
        download_completed,
        activation,
        submit,
        stat_date,
        @
        stat_hour,
        photo_click,
        photo_click_ratio,
        action_ratio,
        impression_1k_cost,
        photo_click_cost,
        @
        click_1k_cost,
        action_cost,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay_first_day_roi,
        event_pay,
        event_pay_purchase_amount,
        event_pay_roi,
        event_register,
        event_register_cost,
        event_register_ratio,
        event_jin_jian_app,
        event_jin_jian_app_cost,
        event_credit_grant_app,
        event_credit_grant_app_cost,
        event_credit_grant_app_ratio,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_order_paid_cost,
        form_count,
        form_cost,
        form_action_ratio,
        event_jin_jian_landing_page,
        event_jin_jian_landing_page_cost,
        event_credit_grant_landing_page,
        event_credit_grant_landing_page_cost,
        event_credit_grant_landing_ratio,
        event_next_day_stay_cost,
        event_next_day_stay_ratio,
        event_next_day_stay,
        play_3s_ratio,
        @
        event_valid_clues,
        @
        event_valid_clues_cost,
        @
        ad_product_cnt,
        @
        event_goods_view,
        @
        merchant_reco_fans,
        @
        event_order_amount_roi,
        @
        event_goods_view_cost,
        @
        merchant_reco_fans_cost,
        @
        event_new_user_pay,
        @
        event_new_user_pay_cost,
        @
        event_new_user_pay_ratio,
        @
        event_button_click,
        @
        event_button_click_cost,
        @
        event_button_click_ratio,
        @
        play_5s_ratio,
        @
        play_end_ratio,
        @
        event_order_paid_roi,
        @
        event_new_user_jinjian_app,
        @
        event_new_user_jinjian_app_cost,
        @
        event_new_user_jinjian_app_roi,
        @
        event_new_user_credit_grant_app,
        @
        event_new_user_credit_grant_app_cost,
        @
        event_new_user_credit_grant_app_roi,
        @
        event_new_user_jinjian_page,
        @
        event_new_user_jinjian_page_cost,
        @
        event_new_user_jinjian_page_roi,
        @
        event_new_user_credit_grant_page,
        @
        event_new_user_credit_grant_page_cost,
        @
        event_new_user_credit_grant_page_roi,
        @
        event_appoint_form,
        @
        event_appoint_form_cost,
        @
        event_appoint_form_ratio,
        @
        event_appoint_jump_click,
        @
        event_appoint_jump_click_cost,
        @
        event_appoint_jump_click_ratio
        )
        set
        account_id
        =
        #{accountId}
    </insert>

    <select id="getPhotoList" resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaiShouReportDailyMaterialVo">
        select
        id as id,
        photo_id as photoId
        from
        ctop_kuaishou_report_daily_material
        where account_id = #{accountId}
        <if test="statDate != null and statDate != '' ">
            and stat_date = #{statDate}
        </if>
        group by photo_id

    </select>

    <select id="getPhotoListByDate"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaiShouReportDailyMaterialVo">
        select id       as id,
               photo_id as photoId
        from ctop_kuaishou_report_daily_material
        WHERE stat_date = #{statDate}
          and signature is null
        group by photo_id
    </select>

    <select id="getVideoGetByPhotoId" resultType="cn.com.ctop.kuaishou.modules.report.entity.VideoGetvo">
        select signature as 'signature', channel_type as 'channelType'
        from ctop_etl_kuaishou_video_base_info
        where photo_id = #{photoId}
         and signature is not null limit 1
    </select>


    <select id="getEtlDataByParams"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaiShouReportDailyMaterial">
        select
        report.channel_type,
        report.signature,
        report.cover_url,
        report.photo_url,
        min(report.stat_date)as 'stat_date',
        sum(report.charge)as 'charge',
        sum(report.photo_click)as 'photo_click',
        sum(report.photo_show)as 'photo_show',
        sum(report.aclick)as 'aclick',
        sum(report.bclick)as 'bclick'
        from ctop_kuaishou_report_daily_material report
        left join ctop_user_allocation allocation on allocation.account_id = report.account_id
        where
        report.signature = #{signature}
        and
        report.stat_date&lt;=#{date}
        <if test="projectId!=null">
            and allocation.project_id = #{projectId}
        </if>
        group by report.signature
    </select>
    <select id="getVideoList" parameterType="java.util.Map"
            resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">

        SELECT
        sum(t1.charge) as 'charge',
        t1.photo_id AS 'photoId',
        t1.photo_url AS 'url',
        t1.cover_url AS 'coverUrl',
        t1.signature AS 'signature',
        t2.material_type AS 'materialType'
        FROM
        ctop_kuaishou_report_daily_material t1
        LEFT JOIN (
        SELECT
        material_type,
        signature,
        status
        FROM
        ctop_kuaishou_video_get
        WHERE
        account_id = #{accountId} group by signature) t2
        ON t1.signature = t2.signature
        WHERE
        t1.account_id = #{accountId}
        AND t2.material_type = #{materialType}
        AND t2.status = 0
        <if test="startDate != null and startDate != '' ">
            and t1.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and t1.stat_date &lt;= #{endDate}
        </if>
        <if test="signatureList != null and signatureList != '' ">
            and t1.signature in
            <foreach collection="signatureList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        signature
        ORDER BY
        sum(t1.charge) DESC
    </select>
    <select id="getSignatureByDate"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaiShouReportDailyMaterial">
        select distinct signature
        from ctop_kuaishou_report_daily_material
        where signature is not null
          and signature != ''
        and
            stat_date = #{date}
    </select>
    <select id="getSignatureByDateGroupByProjectId"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaiShouReportDailyMaterial">
        select distinct signature,
                        project_id
        from ctop_kuaishou_report_daily_material report
                 left join ctop_user_allocation allocation on report.account_id = allocation.account_id
        where signature is not null
          and signature != ''
    and
    stat_date = #{date}
    </select>
    <select id="getVideoInfoByList" resultType="com.alibaba.fastjson.JSONObject">
        select
        photo_id as 'photoId',
        signature as 'signature',
        channel_type as 'channelType'
        from ctop_etl_kuaishou_video_base_info
        where photo_id in
        <foreach collection="photoIdList" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        and signature is not null
        group by photo_id
    </select>

    <select id="labelMaterialListTotal" resultType="java.lang.Long">
        SELECT IFNULL(COUNT(1),0) FROM
        (
        SELECT
        t1.user_id
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        ) t3 ON t3.id = t1.user_id
        WHERE t2.parent_id in(60155,60156)
        GROUP BY t1.user_id
        ) t
    </select>

    <select id="labelMaterialList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        k.user_id,
        k.realname,
        k.role_name 'roleName',
        IFNULL(k1.zhenren_charge,0)as 'zhenrenCharge',
        IFNULL(k2.zhenren_number,0)as 'zhenrenNumber',
        IFNULL(ROUND(k1.zhenren_charge/k2.zhenren_number,2),0) as 'zhenrenCost',
        IFNULL(k3.zhizuo_charge,0)as 'zhizuoCharge',
        IFNULL(k4.zhizuo_number,0)as 'zhizuoNumber',
        IFNULL(ROUND(k3.zhizuo_charge/k4.zhizuo_number,2),0) as 'zhizuoCost'
        FROM
        (SELECT
        t1.user_id,
        t3.realname,
        t3.role_name
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        WHERE t2.parent_id in(60155,60156)
        GROUP BY t1.user_id
        ) k
        LEFT JOIN
        (
        SELECT
        t1.user_id, SUM(t5.charge) 'zhenren_charge'
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        LEFT JOIN ctop_kuaishou_report_daily_material t5 ON t1.`code` = t5.signature
        WHERE t2.parent_id = 60155
        GROUP BY t1.user_id
        ) k1 ON k1.user_id =k.user_id
        LEFT JOIN
        (SELECT user_id, COUNT(1) 'zhenren_number' FROM (
        SELECT
        t1.user_id,t4.signature
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        WHERE t2.parent_id = 60155
        GROUP BY t1.user_id,t1.`code`
        ) t
        GROUP BY user_id
        )k2 ON k.user_id = k2.user_id
        LEFT JOIN
        (
        SELECT
        t1.user_id, SUM(t5.charge) 'zhizuo_charge'
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        LEFT JOIN ctop_kuaishou_report_daily_material t5 ON t1.`code` = t5.signature
        WHERE t2.parent_id = 60156
        GROUP BY t1.user_id
        )k3 ON k3.user_id =k.user_id
        LEFT JOIN
        (SELECT user_id, COUNT(1) 'zhizuo_number' FROM (
        SELECT
        t1.user_id,t4.signature
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        WHERE t2.parent_id = 60156
        GROUP BY t1.user_id,t1.`code`
        ) t
        GROUP BY user_id
        )k4 ON k4.user_id = k.user_id
        ORDER BY k1.zhenren_charge DESC,k3.zhizuo_charge DESC

    </select>

    <select id="labelMaterialAll" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        '总计' as 'realname',
        '-' as 'roleName',
        IFNULL(SUM(t.zhenren_charge),0) as 'zhenrenCharge',
        IFNULL(SUM(t.zhenren_number),0) as 'zhenrenNumber',
        IFNULL(ROUND(SUM(t.zhenren_charge)/SUM(t.zhenren_number),2),0) as 'zhenrenCost',
        IFNULL(SUM(t.zhizuo_charge),0) as 'zhizuoCharge',
        IFNULL(SUM(t.zhizuo_number),0) as 'zhizuoNumber',
        IFNULL(ROUND(SUM(t.zhizuo_charge)/SUM(t.zhizuo_number),2),0) as 'zhizuoCost'
        FROM
        (SELECT
        IFNULL(k1.zhenren_charge,0)as 'zhenren_charge',
        IFNULL(k2.zhenren_number,0)as 'zhenren_number',
        IFNULL(k3.zhizuo_charge,0)as 'zhizuo_charge',
        IFNULL(k4.zhizuo_number,0)as 'zhizuo_number'
        FROM
        (SELECT
        t1.user_id,
        t3.realname,
        t3.role_name
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        WHERE t2.parent_id in(60155,60156)
        GROUP BY t1.user_id
        ) k
        LEFT JOIN
        (
        SELECT
        t1.user_id, SUM(t5.charge) 'zhenren_charge'
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN
        (
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        LEFT JOIN ctop_kuaishou_report_daily_material t5 ON t1.`code` = t5.signature
        WHERE t2.parent_id = 60155
        GROUP BY t1.user_id
        )k1 ON k1.user_id =k.user_id
        LEFT JOIN
        (SELECT user_id, COUNT(1) 'zhenren_number' FROM (
        SELECT
        t1.user_id,t4.signature
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        WHERE t2.parent_id = 60155
        GROUP BY t1.user_id,t1.`code`
        ) t
        GROUP BY user_id
        )k2 ON k.user_id = k2.user_id
        LEFT JOIN
        (
        SELECT
        t1.user_id, SUM(t5.charge) 'zhizuo_charge'
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        LEFT JOIN ctop_kuaishou_report_daily_material t5 ON t1.`code` = t5.signature
        WHERE t2.parent_id = 60156
        GROUP BY t1.user_id
        )k3 ON k3.user_id =k.user_id
        LEFT JOIN
        (SELECT user_id, COUNT(1) 'zhizuo_number' FROM (
        SELECT
        t1.user_id,t4.signature
        FROM ctop_material_tag_info t1
        LEFT JOIN ctop_tag_info t2 ON t2.id = t1.tag_id
        INNER JOIN
        (
        SELECT signature FROM ctop_kuaishou_video_get
        WHERE stat_date &gt;= #{startTime}
        AND stat_date &lt;= concat(#{endTime},' 23:59:59')
        GROUP BY signature
        ) t4 ON t4.signature= t1.`code`
        INNER JOIN(
        SELECT tt1.id,tt3.role_name,tt1.realname FROM sys_user tt1
        LEFT JOIN sys_user_role tt2 ON tt1.id = tt2.user_id
        LEFT JOIN sys_role tt3 ON tt2.role_id = tt3.id
        LEFT JOIN user_company tt4 ON tt1.id = tt4.user_id
        WHERE 1=1
        <if test="companyId!=null">
            AND tt4.company_id = #{companyId}
        </if>
        <if test="realname!=null">
            and tt1.realname LIKE CONCAT('%',#{realname},'%')
        </if>
        <if test="roleCode!=null">
            AND tt3.role_code = #{roleCode}
        </if>
        <if test="roleCode==null">
            AND tt3.role_code in ('clip','shot','plan')
        </if>
        ) t3 ON t3.id = t1.user_id
        WHERE t2.parent_id = 60156
        GROUP BY t1.user_id,t1.`code`
        ) t
        GROUP BY user_id
        )k4 ON k4.user_id = k.user_id
        ORDER BY k1.zhenren_charge DESC,k3.zhizuo_charge DESC
        )t
    </select>
    <select id="getReportList"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.EtlKuaishouAccountMaterialReportDaily">
        SELECT
        #{projectId} as 'projectId',
        #{projectName} as 'projectName',
	    t1.account_id AS 'accountId',
        t1.signature as 'signature',
	    t1.photo_id AS 'photoId',
	    sum(t1.charge) AS 'charge',
	    sum(t1.photo_show) AS 'photoShow',
	    sum(t1.aclick) AS 'aclick',
	    sum(t1.bclick) AS 'bclick',
	    sum(t1.download_started) AS 'downloadStarted',
	    sum(t1.download_completed) AS 'downloadCompleted',
	    sum(t1.activation) AS 'activation',
	    sum(t1.submit) AS 'submit',
	    t1.stat_date AS 'statDate',
	    sum(t1.photo_click) AS 'photoClick',
	    sum(t1.event_pay_first_day) AS 'eventPayFirstDay',
	    sum(
		t1.event_pay_purchase_amount_first_day
	    ) AS 'eventPayPurchaseAmountFirstDay',
	    sum(t1.event_pay) AS 'eventPay',
	    sum(
		t1.event_pay_purchase_amount
    	) AS 'eventPayPurchaseAmount',
	    sum(t1.event_register) AS 'eventRegister',
	    sum(t1.event_jin_jian_app) AS 'eventJinJianApp',
	    sum(t1.event_credit_grant_app) AS 'eventCreditGrantApp',
	    sum(t1.event_order_paid) AS 'eventOrderPaid',
	    sum(
		t1.event_order_paid_purchase_amount
	    ) AS 'eventOrderPaidPurchaseAmount',
	    sum(
		t1.event_jin_jian_landing_page
	    ) AS 'eventJinJianLandingPage',
	    sum(
		t1.event_credit_grant_landing_page
	    ) AS 'eventCreditGrantLandingPage',
	    sum(t1.event_next_day_stay) AS 'eventNextDayStay',
	    sum(t1.form_count) AS 'formCount',
	    round(
		sum(
			t1.play_3s_ratio * t1.aclick
		)
	    ) AS play3sCount
        FROM
	    ctop_kuaishou_report_daily_material t1
        WHERE
	    t1.account_id = #{accountId}
        AND t1.stat_date = #{statDate}
        GROUP BY
	    t1.photo_id;
    </select>
    <select id="getBatchV2VideoList" resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">
        SELECT
        t1.photo_id AS 'photoId',
        t1.url AS 'url',
        t1.cover_url AS 'coverUrl',
        t1.signature AS 'signature',
        t1.stat_date as 'statDate',
        t1.photo_name as 'photoName',
        t1.channel_type as 'channelType'
        FROM
        ctop_kuaishou_video_get t1
        where t1.account_id = #{accountId}
        and t1.status = 0
        <if test="photoId != null and photoId != '' ">
            AND t1.photo_id = #{photoId}
        </if>

        <if test="startDate != null and startDate != '' ">
            and t1.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and t1.stat_date &lt;= #{endDate}
        </if>
        <if test="photoName != null and photoName != '' ">
            AND t1.photo_name like CONCAT(CONCAT('%', #{photoName}), '%')
        </if>
        <if test="signatureList != null and signatureList != '' ">
            and t1.signature in
            <foreach collection="signatureList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        order by t1.stat_date desc
    </select>



    <select id="getYesterdayVideo"
            resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">

        SELECT
        t1.signature as 'signature',
        t1.photo_id AS 'photoId',
        t1.photo_url AS 'photoUrl',
        t1.cover_url AS 'coverUrl',
        t1.charge AS 'charge',
        t1.channel_type AS 'channelType',
        t1.video_name AS 'photoName'
        FROM
        `application`.kuaishou_material_video_report_daily_dw t1
        WHERE
        t1.account_id = #{accountId}
        AND t1.stat_date = #{date}
        <if test="photoId != null and photoId != '' ">
            AND t1.photo_id = #{photoId}
        </if>
        <if test="photoName != null and photoName != '' ">
            AND t1.video_name LIKE CONCAT(CONCAT('%', #{photoName}), '%')
        </if>
        ORDER BY t1.charge DESC
    </select>
    <select id="get7dayVideo" resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">
        SELECT
        t1.signature as 'signature',
        t1.photo_id AS 'photoId',
        t1.photo_url AS 'photoUrl',
        t1.cover_url AS 'coverUrl',
        t1.charge AS 'charge',
        t1.channel_type AS 'channelType',
        t1.video_name AS 'photoName'
        FROM
        `application`.app_kuaishou_video_report_busi_7d t1
        WHERE
        t1.account_id = #{accountId}
        <if test="photoId != null and photoId != '' ">
            AND t1.photo_id = #{photoId}
        </if>
        <if test="photoName != null and photoName != '' ">
            AND t1.video_name LIKE CONCAT(CONCAT('%', #{photoName}), '%')
        </if>
        ORDER BY t1.charge DESC
    </select>
    <select id="get14dayVideo" resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">
        SELECT
        t1.signature as 'signature',
        t1.photo_id AS 'photoId',
        t1.photo_url AS 'photoUrl',
        t1.cover_url AS 'coverUrl',
        t1.charge AS 'charge',
        t1.channel_type AS 'channelType',
        t1.video_name AS 'photoName'
        FROM
        `application`.app_kuaishou_video_report_busi_14d t1
        WHERE
        t1.account_id = #{accountId}
        <if test="photoId != null and photoId != '' ">
            AND t1.photo_id = #{photoId}
        </if>
        <if test="photoName != null and photoName != '' ">
            AND t1.video_name LIKE CONCAT(CONCAT('%', #{photoName}), '%')
        </if>
        ORDER BY t1.charge DESC
    </select>
    <select id="get30dayVideo" resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">
        SELECT
        t1.signature as 'signature',
        t1.photo_id AS 'photoId',
        t1.photo_url AS 'photoUrl',
        t1.cover_url AS 'coverUrl',
        t1.charge AS 'charge',
        t1.channel_type AS 'channelType',
        t1.video_name AS 'photoName'
        FROM
        `application`.app_kuaishou_video_report_busi_30d t1
        WHERE
        t1.account_id = #{accountId}
        <if test="photoId != null and photoId != '' ">
            AND t1.photo_id = #{photoId}
        </if>
        <if test="photoName != null and photoName != '' ">
            AND t1.video_name LIKE CONCAT(CONCAT('%', #{photoName}), '%')
        </if>
        ORDER BY t1.charge DESC
    </select>
    <select id="get3MonthVideo" resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">
        SELECT
        t.*
        FROM
        (
        SELECT
        t1.signature AS 'signature',
        t1.photo_id AS 'photoId',
        t1.photo_url AS 'photoUrl',
        t1.cover_url AS 'coverUrl',
        sum( t1.charge ) AS 'charge',
        t1.channel_type AS 'channelType',
        t1.video_name AS 'photoName'
        FROM
        `application`.app_kuaishou_video_report_busi_m t1
        WHERE
        t1.account_id = #{accountId}
        <if test="startMonth != null and startMonth != '' ">
            and t1.stat_mon &gt;= #{startMonth}
        </if>
        <if test="endMonth != null and endMonth != '' ">
            and t1.stat_mon &lt;= #{endMonth}
        </if>
        <if test="photoId != null and photoId != '' ">
            AND t1.photo_id = #{photoId}
        </if>
        <if test="photoName != null and photoName != '' ">
            AND t1.video_name LIKE CONCAT(CONCAT('%', #{photoName}), '%')
        </if>
        GROUP BY
        t1.photo_id,t1.signature
        ) t
        ORDER BY
        t.charge DESC
    </select>
</mapper>

