<?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.ai.mapper.KuaishouProjectStrategyMapper">

    <select id="queryProjectList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.*,
               (SELECT COUNT(1) FROM ctop_user_allocation WHERE account_status = 0 and t1.projectId = project_id) as 'allAccountCount',
               (SELECT COUNT(1) FROM ctop_user_allocation WHERE account_status = 0 and t1.projectId = project_id and auto_delivery = 1) as 'successCount',
               IFNULL(SUM(t2.charge),0) as 'charge',
               IFNULL(SUM(t2.photo_show),0) as 'photoShow',
               IFNULL(SUM(t2.photo_click),0) as 'photoClick',
               IFNULL(SUM(t2.aclick),0) as 'aclick',
               IFNULL(SUM(t2.bclick),0) as 'bclick',
               CONCAT(CAST(IFNULL(ROUND(SUM(t2.photo_click)/SUM(t2.photo_show)*100,2),0)as CHAR),'%') as 'photoClickRatio',
               CONCAT(CAST(IFNULL(ROUND(SUM(t2.bclick)/SUM(t2.aclick)*100,2),0)as CHAR),'%') as 'actionRatio',
               IFNULL(ROUND(SUM(t2.charge)/SUM(t2.aclick)*1000,2),0) as 'impression1kCost',
               IFNULL(ROUND(SUM(t2.charge)/SUM(t2.photo_click),2),0) as 'photoClickCost',
               IFNULL(ROUND(SUM(t2.charge)/SUM(t2.bclick),2),0) as 'actionCost'
        FROM
            (
                SELECT
                    k1.id,
                    k1.data_status as 'dataStatus',
                    k1.project_id as 'projectId',
                    k3.project_name as 'projectName',
                    (SELECT realname FROM sys_user WHERE k3.responsible_id = id) as 'responsibleName',
                    k1.stat_date as 'statDate',
                    k2.account_id as 'accountId',
                    k1.create_time as 'createTime'
                FROM ctop_ai_kuaishou_project_strategy k1
                         LEFT JOIN ctop_user_allocation k2 ON k1.project_id = k2.project_id
                         LEFT JOIN ctop_project k3 ON k1.project_id = k3.id
                where k3.media_id=2 and k2.auto_delivery != 3
                    <if test="projectId != null ">
                    and  k1.project_id = #{projectId}
                    </if>
                  <if test="dataStatus != null ">
                    and  k1.data_status = #{dataStatus}
                    </if>
                    <if test="userId != null ">
                     and k3.responsible_id = #{userId}
                    </if>
            ) t1
                LEFT JOIN ctop_etl_kuaishou_report_account_daily t2 ON t2.account_id = t1.accountId
        and t2.stat_date &gt;=#{startTime}
        and t2.stat_date &lt;=#{endTime}
        GROUP BY t1.id
        <if test="sortCode != '' and sortCode != null ">
            ORDER BY ${sortCode} ${sortType}
        </if>
    </select>

    <select id="queryAccountList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            IFNULL(t1.auth_name,'') as 'authName',
            IFNULL(t1.account_id,'') as 'accountId',
            IFNULL(t1.auto_delivery,0) as 'projectStrategy',
            IFNULL(t3.project_name,'') as 'projectName',
            IFNULL(SUM(t2.charge),0) as 'charge',
            IFNULL(SUM(t2.photo_show),0) as 'photoShow',
            IFNULL(SUM(t2.photo_click),0) as 'photoClick',
            IFNULL(SUM(t2.aclick),0) as 'aclick',
            IFNULL(SUM(t2.bclick),0) as 'bclick',
            CONCAT(CAST(IFNULL(ROUND(SUM(t2.photo_click)/SUM(t2.photo_show)*100,2),0)as CHAR),'%') as 'photoClickRatio',
            CONCAT(CAST(IFNULL(ROUND(SUM(t2.bclick)/SUM(t2.aclick)*100,2),0)as CHAR),'%') as 'actionRatio',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.aclick)*1000,2),0) as 'impression1kCost',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.photo_click),2),0) as 'photoClickCost',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.bclick),2),0) as 'actionCost',
            (SELECT IFNULL(MIN(stat_date),'') FROM ctop_kuaishou_report_daily_account where account_id = t1.account_id AND charge >0) as 'statDate'
        FROM ctop_user_allocation t1
                 LEFT JOIN ctop_etl_kuaishou_report_account_daily t2 ON t2.account_id = t1.account_id
                and t2.stat_date &gt;=#{startTime}
                and t2.stat_date &lt;=#{endTime}
                 LEFT JOIN ctop_project t3 ON t1.project_id = t3.id
        where t1.auto_delivery != 3 AND account_status = 0
        <if test="projectIds != null and projectIds.size()>0 ">
            AND t1.project_id in
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
          <if test="authName != null ">
              and  t1.auth_name LIKE CONCAT('%',#{authName},'%')
        </if>
        GROUP BY t1.account_id
        <if test="sortCode != '' and sortCode != null ">
            ORDER BY ${sortCode} ${sortType}
        </if>
    </select>

    <select id="queryAccountAll" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            '-' as 'authName',
            IFNULL(t1.account_id,'') as 'accountId',
            IFNULL(t1.project_strategy,0) as 'projectStrategy',
            '-' as 'projectName',
            IFNULL(SUM(t2.charge),0) as 'charge',
            IFNULL(SUM(t2.photo_show),0) as 'photoShow',
            IFNULL(SUM(t2.photo_click),0) as 'photoClick',
            IFNULL(SUM(t2.aclick),0) as 'aclick',
            IFNULL(SUM(t2.bclick),0) as 'bclick',
            CONCAT(CAST(IFNULL(ROUND(SUM(t2.photo_click)/SUM(t2.photo_show)*100,2),0)as CHAR),'%') as 'photoClickRatio',
            CONCAT(CAST(IFNULL(ROUND(SUM(t2.bclick)/SUM(t2.aclick)*100,2),0)as CHAR),'%') as 'actionRatio',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.aclick)*1000,2),0) as 'impression1kCost',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.photo_click),2),0) as 'photoClickCost',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.bclick),2),0) as 'actionCost'
        FROM ctop_user_allocation t1
                 LEFT JOIN ctop_etl_kuaishou_report_account_daily t2 ON t2.account_id = t1.account_id
                 LEFT JOIN ctop_project t3 ON t1.project_id = t3.id
        where t1.auto_delivery != 3
          and t2.stat_date &gt;=#{startTime}
          and t2.stat_date &lt;=#{endTime}
        <if test="projectIds != null and projectIds.size()>0 ">
            AND t1.project_id in
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
          <if test="authName != null ">
              and  t1.auth_name LIKE CONCAT('%',#{authName},'%')
        </if>
    </select>

    <select id="queryProjectAll" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            '-' as 'authName',
            IFNULL(t1.account_id,'') as 'accountId',
            IFNULL(t1.project_strategy,0) as 'projectStrategy',
            '-' as 'projectName',
            IFNULL(SUM(t2.charge),0) as 'charge',
            IFNULL(SUM(t2.photo_show),0) as 'photoShow',
            IFNULL(SUM(t2.photo_click),0) as 'photoClick',
            IFNULL(SUM(t2.aclick),0) as 'aclick',
            IFNULL(SUM(t2.bclick),0) as 'bclick',
            CONCAT(CAST(IFNULL(ROUND(SUM(t2.photo_click)/SUM(t2.photo_show)*100,2),0)as CHAR),'%') as 'photoClickRatio',
            CONCAT(CAST(IFNULL(ROUND(SUM(t2.bclick)/SUM(t2.aclick)*100,2),0)as CHAR),'%') as 'actionRatio',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.aclick)*1000,2),0) as 'impression1kCost',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.photo_click),2),0) as 'photoClickCost',
            IFNULL(ROUND(SUM(t2.charge)/SUM(t2.bclick),2),0) as 'actionCost',
            (SELECT COUNT(1) FROM ctop_user_allocation where auto_delivery = 1 AND account_status = 0
            <if test="projectIds != null and projectIds.size()>0 ">
                 AND project_id in
                <foreach collection="projectIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            ) as 'successCount',
            (SELECT COUNT(1) FROM ctop_user_allocation where account_status = 0
            <if test="projectIds != null and projectIds.size()>0 ">
            AND project_id in
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
            </if>
            ) as 'allAccountCount'
        FROM ctop_user_allocation t1
                 LEFT JOIN ctop_etl_kuaishou_report_account_daily t2 ON t2.account_id = t1.account_id and t2.stat_date &gt;=#{startTime}
                 and t2.stat_date &lt;=#{endTime}
                 LEFT JOIN ctop_project t3 ON t1.project_id = t3.id
        where t1.auto_delivery != 3
        <if test="projectIds != null and projectIds.size()>0 ">
            and t1.project_id in
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
          <if test="userId != null ">
              and  t3.responsible_id =#{userId}
        </if>
    </select>


    <select id="checkProjectisOpen" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM ctop_ai_kuaishou_project_strategy WHERE data_status = 1
        AND project_id = #{projectId}
        LIMIT 1
    </select>

    <select id="getAllEffectStrategy" resultType="cn.com.ctop.kuaishou.modules.ai.entity.KuaishouProjectStrategy">
        SELECT id, project_id from ctop_ai_kuaishou_project_strategy
        WHERE data_status = 1
        <if test="unitType != null ">
          AND (unit_type = #{unitType} OR unit_type = 0)
        </if>
        <if test="sourceMaterial != null ">
          AND LOCATE(#{sourceMaterial},source_material)
        </if>
    </select>

    <select id="queryProjectId" resultType="java.lang.Long">
        SELECT project_id from ctop_ai_kuaishou_project_strategy
        group by project_id
    </select>

</mapper>