<?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.KuaishouDirectionalPackageMapper">

    <select id="queryEntity" resultType="cn.com.ctop.kuaishou.modules.ai.entity.KuaishouDirectionalPackage">
        SELECT project_id,template_name from ctop_kuaishou_directional_package
        WHERE data_status = 1
        <if test="projectId != null">
            and project_id = #{projectId}
        </if>
        <if test="templateName != null">
            and template_name = #{templateName}
        </if>
        limit 1
    </select>
    <select id="queryList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.id,
        t1.template_name 'templateName',
        t2.project_name 'projectName',
        t1.project_id 'projectId',
        CASE WHEN t1.platform_os =1 THEN 'Android' WHEN t1.platform_os =2 THEN 'IOS' ELSE '不限' END 'platformOs',
        t1.update_time 'updateTime',
        IFNULL(t4.count,0) as 'accountCount',
        IFNULL(t5.count,0) as 'unitCount'
        FROM ctop_kuaishou_directional_package t1
        LEFT JOIN ctop_project t2 ON t2.id = t1.project_id
        LEFT JOIN (
        SELECT dir_id,COUNT(1) 'count' FROM
        ctop_kuaishou_directional_package_rel
        WHERE rel_type = 'account'
        AND data_status=1
        GROUP BY dir_id
        ) t4 ON t4.dir_id = t1.id
        LEFT JOIN (
        SELECT dir_id,COUNT(1) 'count' FROM
        ctop_kuaishou_directional_package_rel t
        INNER JOIN ctop_kuaishou_group tt ON t.rel_id = tt.unit_id
        WHERE t.rel_type = 'unit'
        AND t.data_status=1
        GROUP BY dir_id
        ) t5 ON t5.dir_id = t1.id
        LEFT JOIN ctop_project_member t6 ON t1.project_id = t6.project_id
        where t1.data_status = 1
        <if test="userId !=null">
            AND t6.user_id =#{userId}
        </if>
        <if test="templateName !=null">
            AND t1.template_name like concat('%',#{templateName},'%')
        </if>
        <if test="platformOs !=null">
            AND t1.platform_os = #{platformOs}
        </if>
        <if test="projectId !=null">
            AND t1.project_id = #{projectId}
        </if>
        GROUP BY t1.id
        order by t1.update_time desc
    </select>

    <select id="queryListTotal" resultType="java.lang.Long">
        SELECT IFNULL(count(1),0)
        FROM (
        SELECT
        t1.id
        FROM ctop_kuaishou_directional_package t1
        LEFT JOIN ctop_project_member t2 ON t1.project_id = t2.project_id
        where t1.data_status = 1
        <if test="userId !=null">
            AND t2.user_id =#{userId}
        </if>
        <if test="templateName !=null">
            AND t1.template_name like concat('%',#{templateName},'%')
        </if>
        <if test="platformOs !=null">
            AND t1.platform_os = #{platformOs}
        </if>
        <if test="projectId !=null">
            AND t1.project_id = #{projectId}
        </if>
        GROUP BY t1.id
        ) t

    </select>


    <select id="getRelatedAccount" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.dir_id as 'id', IFNULL(t2.auth_name, '') as 'authName', IFNULL(t2.account_id, '') as 'accountId', IFNULL(t1.template_id, '') as 'templateId', IFNULL(t1.template_name, '') as 'templateName'
        FROM ctop_kuaishou_directional_package_rel t1
                 INNER JOIN ctop_user_allocation t2 ON t1.rel_id = t2.account_id
        WHERE t1.data_status = 1
          AND t1.dir_id = #{id}
    </select>

    <select id="getRelatedGroup" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t2.dir_id as 'id',
        IFNULL(t3.auth_name,'') as 'authName',
        IFNULL(t1.account_id,'') as 'accountId',
        IFNULL(t2.template_id,'') as 'templateId',
        t1.unit_name as 'unitName'
        FROM ctop_kuaishou_directional_package_rel t2
        INNER JOIN ( SELECT account_id,unit_id,unit_name FROM ctop_kuaishou_group
        where 1=1
        <if test="accountId!=null">
            and account_id = #{accountId}
        </if>
        ) t1 ON t1.unit_id = t2.rel_id
        LEFT JOIN ctop_user_allocation t3 ON t1.account_id = t3.account_id
        WHERE t2.rel_type = 'unit' AND t2.dir_id =#{id}
    </select>

    <select id="queryListByProjectId" resultType="cn.com.ctop.kuaishou.modules.ai.entity.KuaishouDirectionalPackage">
        SELECT *
        FROM ctop_kuaishou_directional_package
        WHERE data_status = 1
        and project_id = #{projectId}
        <if test="templateName !=null">
            and template_name like concat('%',#{templateName},'%')
        </if>
    </select>

    <select id="getProjectTargetByIds" resultType="cn.com.ctop.kuaishou.modules.ai.entity.KuaishouDirectionalPackage">
        SELECT *
        FROM ctop_kuaishou_directional_package
        WHERE data_status = 1
        and id in
        <foreach collection="ids" item="ids" separator="," open="(" close=")">
            #{ids}
        </foreach>
    </select>
</mapper>