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

    <select id="queryPageListTotal" resultType="java.lang.Long">
        SELECT IFNULL(COUNT(1), 0)
        FROM
        (SELECT t1.id FROM ctop_kuaishou_population_package t1
        LEFT JOIN ctop_project_member t3 ON t1.project_id = t3.project_id
        WHERE t1.data_status =1
        <if test="userId !=null">
            AND t3.user_id =#{userId}
        </if>
        <if test="orientationName !=null">
            AND orientation_name LIKE CONCAT('%', #{orientationName}, '%')
        </if>
        <if test="projectId !=null">
            AND t1.project_id =#{projectId}
        </if>
        GROUP BY t1.id
        ) t
    </select>

    <select id="queryPageList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.orientation_name 'orientationName',
        IFNULL(t1.type,'') as 'type',
        CASE t1.type
        WHEN 1 THEN 'IMEI'
        WHEN 2 THEN 'IDFA'
        WHEN 3 THEN 'IMEI_MD5'
        WHEN 4 THEN 'IDFA_MD5'
        WHEN 5 THEN '手机号-MD5'
        WHEN 7 THEN 'OAID'
        WHEN 8 THEN 'OAID_MD5'
        ELSE ''
        END as 'typeStr',
        t1.id,
        t1.stat_date 'statDate',
        t2.project_name 'projectName',
        t1.project_id 'projectId',
        (SELECT COUNT(1) FROM ctop_kuaishou_population_package_rel
        WHERE pop_id = t1.id
        AND data_status = 1)as 'accountCount'
        FROM
        ctop_kuaishou_population_package t1
        LEFT JOIN ctop_project t2 ON t1.project_id = t2.id
        LEFT JOIN ctop_project_member t3 ON t1.project_id = t3.project_id
        WHERE t1.data_status = 1
        <if test="userId !=null">
            AND t3.user_id =#{userId}
        </if>
        <if test="orientationName !=null">
            AND orientation_name LIKE CONCAT(CONCAT('%', #{orientationName}), '%')
        </if>
        <if test="projectId !=null">
            AND t1.project_id =#{projectId}
        </if>
        GROUP BY t1.id
        order by t1.create_time desc
    </select>

    <select id="queryUnitCount" resultType="java.lang.Long">
        SELECT IFNULL(COUNT(1), 0)
        FROM ctop_kuaishou_population_group t1
                 INNER JOIN ctop_kuaishou_group t3 ON t1.unit_id = t3.unit_id
        where orientation_id IN
              (SELECT orientation_id
               FROM ctop_kuaishou_population_package_rel
               WHERE data_status = 1
                 AND pop_id = #{id}
              )
    </select>

    <select id="queryListByStatus" resultType="com.alibaba.fastjson.JSONObject">
        SELECT account_id, project_id
        FROM ctop_kuaishou_population_package_rel
        WHERE data_status = 1
        GROUP BY account_id
    </select>

    <select id="getRelatedGroups" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t2.account_id as 'accountId',
        t2.auth_name as 'authName',
        t3.unit_name as 'unitName'
        FROM ctop_kuaishou_population_group t1
        INNER JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        INNER JOIN ctop_kuaishou_group t3 ON t1.unit_id = t3.unit_id
        where orientation_id IN
        (SELECT DISTINCT orientation_id
        FROM ctop_kuaishou_population_package_rel
        WHERE data_status = 1
        AND pop_id = #{id}
        <if test="accountId !=null">
            AND account_id = #{accountId}
        </if>
        )
        order by t1.create_time desc
    </select>

    <select id="checkPopulationPackage" resultType="cn.com.ctop.kuaishou.modules.ai.entity.KuaishouPopulationPackage">
        select orientation_name
        from ctop_kuaishou_population_package
        where signature = #{signature}
          AND data_status = 1
    </select>

</mapper>