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

    <select id="getNameByAccountId" resultType="java.lang.String">
     select project_name  from ctop_project
     where id = (
     select project_id from ctop_user_allocation
     where account_id = #{accountId}
     )
    </select>

    <select id="getNameByProjectId" resultType="java.lang.String">
     select project_name from ctop_project
     where id = #{projectId}
    </select>

    <select id="getWarningInfoByProjectId" resultType="com.alibaba.fastjson.JSONObject">
        select
            id projectId,
            project_name projectName,
            max_bid maxBid,
            bid_type bidType,
            ocpx_action_type ocpxActionType,
            deep_conversion_type deepConversionType
        from ctop_project
        where id = #{projectId}
    </select>

    <select id="querySupplierCodeById" resultType="java.lang.String">
        select supplier_code from ctop_project where id=#{projectId}
    </select>
    <select id="getProjectListByIds" resultType="com.alibaba.fastjson.JSONObject">
        select
        id as 'projectId',
        advertiser_id as 'advertiserId',
        product_id as 'productId',
        responsible_id as 'responsibleId',
        design_responsible_id as 'designResponsibleId',
        media_id as 'mediaId',
        max_bid as 'maxBid',
        bid_type as 'bidType',
        ocpx_action_type as 'ocpxActionType',
        deep_conversion_type as 'deepConversionType'
        from ctop_project
        where id in
        <foreach collection="ids" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        group by id


    </select>
    <select id="getListByMap" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        id as 'id',
        project_name as 'projectName'
        from ctop_project
        where id in
        <foreach collection="ids" item="ids" separator="," open="(" close=")">
            #{ids}
        </foreach>

    </select>
    <select id="getProjectIdsByProductId" resultType="java.lang.Long">
        SELECT
        id
        from ctop_project
        where product_id = #{productId}
    </select>
    <select id="getProjectIdsByProductIdAndNotIn" resultType="java.lang.Long">
        select id from ctop_project
        where 1 = 1
        <if test="productId != null and productId != ''">
            and product_id = #{productId}
        </if>
        and material_share = 0
        <if test="partakeProjectIds != null and partakeProjectIds.size() > 0">
            and id not in
            <foreach item="item" index="index" collection="partakeProjectIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

    </select>

    <select id="getProjectByAccountId" resultType="com.alibaba.fastjson.JSONObject">
    SELECT
	id AS 'projectId',
	project_name AS 'projectName'
    FROM
	ctop_project
    WHERE
	id = (
		SELECT
			project_id
		FROM
			ctop_user_allocation
		WHERE
			account_id = #{accountId}
		LIMIT 1
	)
    </select>

    <select id="queryProductList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.product_id as 'productId',t2.product_name as 'productName' FROM ctop_project t1
        LEFT JOIN ctop_product t2 ON t1.product_id = t2.id
        WHERE t1.product_id is not null
        AND t2.product_name is not null
        and t1.id IN
        <foreach item="item" index="index" collection="projectList" open="(" separator="," close=")">
            #{item.projectId}
        </foreach>
        GROUP BY t1.product_id
    </select>

    <select id="getProduct" resultType="com.alibaba.fastjson.JSONObject">
        select DISTINCT t1.id as 'projectId', product_id as 'productId', t1.project_name as 'projectName'
        from ctop_project t1 left join ctop_user_allocation t2 ON t1.id = t2.project_id
        <where>
        <if test="projectId != null and projectId != ''">
           and t1.id = #{projectId}
        </if>
        <if test="accountId != null and accountId != ''">
            and t2.account_id = #{accountId}
        </if>
        </where>
    </select>

    <select id="getProjectIdListByCompanyId" resultType="long">
        select id
        from ctop_project
        WHERE company_id = #{companyId}
        <if test="mediaId == 1">
           AND (media_id = 1 or media_id = 3)
        </if>
        <if test="mediaId == 2">
           AND (media_id = 2 or media_id = 4)
        </if>
    </select>

</mapper>
