<?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.finance.payment.mapper.FinancePaymentPoolMapper">

    <select id="queryFinancePaymentPool" resultType="cn.com.ctop.finance.payment.entity.FinancePaymentPool">
        SELECT id,
               advertiser_id,
               advertiser_name,
               product_id,
               product_name,
               payment_type,
               payment_media,
               payment_port,
               cash_amount,
               display_amount,
               rebate_type,
               rebate_rate
        FROM finance_payment_pool
        WHERE advertiser_name = #{advertiserName}
          AND product_id = #{productId}
          AND payment_media = #{paymentMedia}
          AND payment_port = #{paymentPort}

    </select>

    <select id="queryFinancePaymentPoolById" resultType="cn.com.ctop.finance.payment.entity.FinancePaymentPool">
        SELECT id,
               advertiser_id,
               advertiser_name,
               product_id,
               product_name,
               payment_type,
               payment_media,
               payment_port,
               cash_amount,
               display_amount,
               rebate_type,
               rebate_rate
        FROM finance_payment_pool
        WHERE advertiser_id = #{advertiserId}
          AND product_id = #{productId}
          AND payment_media = #{paymentMedia}
          AND payment_port = #{paymentPort}
    </select>

    <select id="queryIdAndName" resultType="com.alibaba.fastjson.JSONObject">
        select ${id} as 'id', ${name} as 'name'
        from ${table}
        <if test="paramters!=null and paramters.size()>0">
            <foreach item="o" collection="paramters" open=" WHERE " separator=" AND ">
                ${o.paramter} = #{o.value}
            </foreach>
        </if>
        GROUP BY ${groupBy}
    </select>

    <select id="queryPoolProjectList" resultType="com.alibaba.fastjson.JSONObject">
        select
        id as 'id',
        project_name as 'name',
        CASE media_id
        WHEN 1 THEN
        '头条'
        WHEN 3 THEN
        '头条内广'
        WHEN 2 THEN
        '快手'
        WHEN 4 THEN
        '快手内广'
        WHEN 5 THEN
        '千川'
        WHEN 6 THEN
        '磁力金牛'
        END as 'media'
        from ctop_project
        Where advertiser_id in
        <foreach item="item" index="index" collection="advertiserIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="productId != null and productId != '' ">
            AND product_id = #{productId}
        </if>
        <choose>
            <when test="type != 11 ">
                AND media_id = #{type}
            </when>
            <when test="type == 11 ">
                AND (media_id = 1 or media_id = 2 )
            </when>
        </choose>
    </select>


    <select id="queryPaymentPort" resultType="com.alibaba.fastjson.JSONObject">
        SELECT payment_port as 'paymentPort', CASE payment_port
                                                  WHEN 1 THEN '汇创'
                                                  WHEN 2 THEN '骄阳'
                                                  WHEN 3 THEN '江西五七'
                                                  WHEN 4 THEN '北京五六七'
                                                  WHEN 5 THEN '骄阳&amp;汇创'
            end as 'portName'
        FROM finance_payment_pool
        WHERE advertiser_id = #{advertiserId}
          AND product_id = #{productId}
          AND payment_media = #{paymentMedia}
    </select>

    <select id="queryPoolAccountList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * from (
        select
        account_id as 'id',
        auth_name as 'name',
        media_id as 'mediaId'
        from ctop_user_allocation
        Where advertiser_id in
        <foreach item="item" index="index" collection="advertiserIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="projectId != null and projectId != '' ">
            AND project_id = #{projectId}
        </if>
        <choose>
            <when test="type != 11 ">
                AND media_id = #{type}
            </when>
            <when test="type == 11 ">
                AND (media_id = 1 or media_id = 2 )
            </when>
        </choose>
        group by account_id
        union all

        select
        cast(account_id as char) as 'id',
        auth_name as 'name',
        media_id as 'mediaId'
        from ctop_qianbao_config
        Where advertiser_id in
        <foreach item="item" index="index" collection="advertiserIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="projectId != null and projectId != '' ">
            AND project_id = #{projectId}
        </if>
        <choose>
            <when test="type != 11 ">
                AND media_id = #{type}
            </when>
            <when test="type == 11 ">
                AND (media_id = 1 or media_id = 2 )
            </when>
        </choose>
        group by account_id
        ) t GROUP BY t.id

    </select>

</mapper>