<?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.common.module.mapper.AdqAccountClaimMapper">

    <!-- 查询未认领的ADQ账户 -->
    <select id="queryUnclaimedAccounts" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            a.id AS 'id',
            a.account_id AS 'accountId',
            a.account_id AS 'advertiserName',
            a.corporation_name AS 'corporationName',
            a.system_status AS 'systemStatus',
            a.admin_advertiser_id AS 'adminAdvertiserId',
            a.create_time AS 'createTime'
        FROM adq_advertiser_info a
        LEFT JOIN ctop_user_allocation ua
            ON a.account_id = ua.account_id AND ua.media_id = '16'
        WHERE ua.id IS NULL
        <if test="accountIdList != null and accountIdList.size() > 0">
            AND a.account_id IN
            <foreach collection="accountIdList" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="advertiserName != null and advertiserName != ''">
            AND a.corporation_name LIKE CONCAT('%', #{advertiserName}, '%')
        </if>
        ORDER BY a.create_time DESC
        LIMIT #{pageNo}, #{pageSize}
    </select>

    <!-- 查询未认领账户总数 -->
    <select id="countUnclaimedAccounts" resultType="java.lang.Long">
        SELECT COUNT(1)
        FROM adq_advertiser_info a
        LEFT JOIN ctop_user_allocation ua
            ON a.account_id = ua.account_id AND ua.media_id = '16'
        WHERE ua.id IS NULL
        <if test="accountIdList != null and accountIdList.size() > 0">
            AND a.account_id IN
            <foreach collection="accountIdList" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="advertiserName != null and advertiserName != ''">
            AND a.corporation_name LIKE CONCAT('%', #{advertiserName}, '%')
        </if>
    </select>

    <!-- 查询media_id=16的项目列表（过滤当前用户有权限的项目） -->
    <select id="queryAdqProjects" resultType="com.alibaba.fastjson.JSONObject">
        SELECT DISTINCT
            p.id AS 'projectId',
            p.project_name AS 'projectName',
            p.advertiser_id AS 'advertiserId'
        FROM ctop_project p
        INNER JOIN ctop_project_member pm ON p.id = pm.project_id AND pm.user_id = #{userId}
        WHERE p.media_id = '16'
        <if test="projectName != null and projectName != ''">
            AND p.project_name LIKE CONCAT('%', #{projectName}, '%')
        </if>
        ORDER BY p.create_time DESC
    </select>

</mapper>
