<?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.channel.mapper.KuaishouChannelMapper">

    <select id="channelManagementHome" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
               t1.id,
        IFNULL(t1.product_name, '')as 'productName',
        IFNULL(t1.project_name, '')as 'projectName',
        IFNULL(t1.account_id, '')as 'accountId',
        IFNULL(t1.project_id, '')as 'projectId',
        MAX(t1.state_date) as 'stateDate',
        IFNULL(t1.create_type, '') as 'createType',
        IFNULL(t1.state,0) as 'state',
        usage_level as 'usageLevel',
        t2.auth_name as 'authName',
        (select realname from sys_user where id = t2.user_id) as 'realname',
        t1.create_time
        FROM ctop_kuaishou_channel t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        <where>
            <if test="rids != null">
                AND t2.user_id IN
                <foreach collection="rids" item="id" separator=","
                         open="(" close=")">
                    #{id}
                </foreach>
            </if>
            <if test="projectId != null">
                and t1.project_id = #{projectId}
            </if>
            <if test="productId != null">
                and t1.product_id = #{productId}
            </if>
            <if test="accountId != null">
                and t1.account_id= #{accountId}
            </if>
            <if test="state != null">
                and t1.state= #{state}
            </if>
        </where>
        GROUP BY t1.account_id
        ORDER BY create_time DESC
    </select>

    <select id="channelNumberDetailsTotal" resultType="java.lang.Long">
        SELECT IFNULL(SUM(1), 0)
        FROM ctop_kuaishou_channel
        <where>
            <if test="accountId != null">
                AND account_id = #{accountId}
            </if>
            <if test="channelName != null">
                AND LOCATE(#{channelName},channel_name)
            </if>
            <if test="channelCode != null">
                AND LOCATE(#{channelCode},channel_code)
            </if>
        </where>
    </select>

    <select id="channelNumberDetails" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT *
        FROM ctop_kuaishou_channel
        <where>
            <if test="accountId != null">
                AND account_id = #{accountId}
            </if>
            <if test="channelName != null">
                AND LOCATE(#{channelName},channel_name)
            </if>
            <if test="channelCode != null">
                AND LOCATE(#{channelCode},channel_code)
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="channelNumberUsageDetailsListTotal" resultType="java.lang.Long">
        SELECT IFNULL(SUM(1), 0)
        FROM ctop_kuaishou_channel_rel t3
                 INNER JOIN ctop_kuaishou_channel t1
                            ON t1.account_id = t3.account_id AND t1.channel_code = t3.channel_code
                 LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        WHERE t3.account_id = #{accountId}
          AND t3.channel_code = #{channelCode}
          AND t3.unit_id is not null
    </select>

    <select id="channelNumberUsageDetailsList" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT  t1.usage_level,
               t3.channel_code,
               t1.channel_name,
              t1.project_name,
              t2.auth_name,
              t3.account_id,
              t3.campaign_id,
              t3.unit_id,
               t3.create_time
        FROM ctop_kuaishou_channel_rel t3
        INNER JOIN ctop_kuaishou_channel t1 ON t1.account_id = t3.account_id AND t1.channel_code = t3.channel_code
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        WHERE t3.account_id = #{accountId}
          AND t3.channel_code = #{channelCode}
          AND t3.unit_id is not null
          order by t3.create_time
    </select>

    <select id="channelNumberBindingItemInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.id,
        IFNULL(t1.product_name, '')as 'productName',
        IFNULL(t1.project_name, '')as 'projectName',
        IFNULL(t1.project_Id, '')as 'project_Id',
        IFNULL(t1.channel_code, '') as 'channelCode',
        IFNULL(t1.channel_name, '') as 'channelName'
        FROM ctop_kuaishou_channel t1
        WHERE t1.id IN
        <foreach collection="request" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </select>


    <select id="queryFollowOrder" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT id,
               channel_code,
               usage_level,
               usage_range,
               usage_times
        FROM `ctop_kuaishou_channel`
        WHERE account_id = #{accountId}
          AND state = 1
          AND channel_state = 1
          AND id > #{usedId}
          AND usage_range = #{usageRange}
        ORDER BY channel_code, channel_number limit 10

    </select>

    <select id="queryByAccountId" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT id,
        channel_code,
        usage_level,
        usage_range,
        is_have_item
        FROM `ctop_kuaishou_channel`
        WHERE account_id = #{accountId}
        AND state = 1
        AND channel_state = 1
        <if test="isHaveItem != null">
            and is_have_item = #{isHaveItem}
        </if>
        ORDER BY usage_range limit 1
    </select>

    <select id="queryMaxChannelNumber" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT usage_times, max(channel_number) as channel_number
        FROM `ctop_kuaishou_channel`
        where strategy_id = #{strategyId}
        group by usage_times
    </select>

    <select id="queryUsedChannelNumber" resultType="java.lang.Integer">
        SELECT count(1)
        from `ctop_kuaishou_channel`
        where strategy_id = #{strategyId}
    </select>

    <select id="queryByAccountIdLevel" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT id,
               channel_code,
               usage_level,
               usage_range,
               usage_times,
               is_have_item,
               channel_name,
               click_track_url,
               actionbar_click_url,
               schema_uri
        FROM `ctop_kuaishou_channel`
        WHERE account_id = #{accountId}
          and usage_level = #{usageLevel}
          AND state = 1
          AND channel_state = 1
    </select>

    <select id="queryChannelByUsageTimes" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT t1.id,
        t1.channel_code,
        t1.usage_level,
        t1.usage_range,
        t1.usage_times,
        t1.is_have_item,
        t1.channel_name,
        t1.click_track_url,
        t1.actionbar_click_url,
        t1.schema_uri
        FROM `ctop_kuaishou_channel` t1
        WHERE t1.strategy_id IN
        <foreach collection="strategys" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
            AND t1.state = 1
            AND t1.channel_state = 1
        <if test="usageTimes > 1">
            AND is_cycle = 1
          </if>
        <if test="appName != null">
            AND app_id in (
                SELECT DISTINCT app_id FROM ctop_kuaishou_channel_app_info where app_name = #{appName}  and app_id is not null
            )
        </if>
            AND t1.usage_times = #{usageTimes}
            and t1.usage_level = #{usageLevel}
            ORDER BY t1.create_time,t1.channel_number
    </select>

    <select id="getAccountLevelRepeatChannel" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT t1.id,
               t1.channel_code,
               t1.usage_level,
               t1.usage_range,
               t1.usage_times,
               t1.is_have_item,
               t1.channel_name,
               t1.click_track_url,
               t1.actionbar_click_url,
               t1.schema_uri
        FROM `ctop_kuaishou_channel` t1
        LEFT JOIN ctop_kuaishou_channel_rel t2 ON t1.account_id = t2.account_id AND t1.channel_code = t2.channel_code
        WHERE
            t1.account_id = #{accountId}
          AND t1.state = 1
          AND t1.channel_state = 1
          AND t1.app_id is not null
          and t1.usage_level = 'account'
        <if test="appName != null">
            AND t1.app_id in (
            SELECT DISTINCT app_id FROM ctop_kuaishou_channel_app_info where app_name = #{appName}  and app_id is not null
            )
        </if>
        GROUP BY t1.channel_code
        ORDER BY t1.create_time,t1.channel_number
            LIMIT 100
    </select>

    <select id="getAccountLevelNoRepeatChannel" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT t1.id,
               t1.channel_code,
               t1.usage_level,
               t1.usage_range,
               t1.usage_times,
               t1.is_have_item,
               t1.channel_name,
               t1.click_track_url,
               t1.actionbar_click_url,
               t1.schema_uri
        FROM `ctop_kuaishou_channel` t1
        WHERE
            t1.account_id = #{accountId}
          AND t1.state = 1
          AND t1.channel_state = 1
          AND app_id is not null
          AND t1.channel_code not in (
            SELECT channel_code FROM ctop_kuaishou_channel_rel WHERE account_id = #{accountId}  AND unit_id is not null
        )
        <if test="appName != null">
            AND t1.app_id in (
            SELECT DISTINCT app_id FROM ctop_kuaishou_channel_app_info WHERE app_name = #{appName}  AND app_id is not null
            )
        </if>
        ORDER BY t1.create_time,t1.channel_number
            LIMIT 100
    </select>

    <select id="queryNoAccountLevelNoRepeatChannel" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT t1.id,
               t1.channel_code,
               t1.usage_level,
               t1.usage_range,
               t1.usage_times,
               t1.is_have_item,
               t1.channel_name,
               t1.click_track_url,
               t1.actionbar_click_url,
               t1.schema_uri
        FROM `ctop_kuaishou_channel` t1
        WHERE  t1.account_id = #{accountId}
          AND t1.state = 1
          AND t1.channel_state = 1
          AND app_id is not null
        <if test="appName != null">
            AND t1.app_id in (
            SELECT DISTINCT app_id FROM ctop_kuaishou_channel_app_info where app_name = #{appName}  and app_id is not null
            )
        </if>
          AND t1.usage_times = 0
          and t1.usage_level = #{usageLevel}
        ORDER BY t1.create_time,t1.channel_number
            LIMIT 100
    </select>

    <select id="getChannelEntity" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT *
        FROM `ctop_kuaishou_channel`
        WHERE account_id = #{accountId}
          AND state = 1
          AND channel_state = 1 LIMIT 1
    </select>

    <select id="queryUsedLevel" resultType="java.lang.Integer">
        SELECT usage_times
        FROM `ctop_kuaishou_channel`
        where strategy_id in
        <foreach collection="strategys" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        AND state = 1
        AND channel_state = 1
        group by usage_times
    </select>

    <select id="queryItemsListByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT tt2.keyword as 'keyword', tt1.channel_code as 'channelCode', tt3.app_name as 'appName'
        FROM ctop_kuaishou_channel_rel tt1
                 INNER JOIN (
            SELECT t3.keyword, t1.channel_code
            FROM ctop_kuaishou_channel t1
                     INNER JOIN ctop_kuaishou_channel_items_rel t2 ON t1.id = t2.channel_id
                     LEFT JOIN ctop_kuaishou_channel_items t3 ON t2.item_id = t3.id
            WHERE t1.is_have_item = 1
              AND t3.state = 1
        ) tt2 ON tt2.channel_code = tt1.channel_code
                 INNER JOIN ctop_kuaishou_channel_app_info tt3 ON tt1.app_id = tt3.app_id
        WHERE tt1.account_id = #{accountId}
          AND tt3.app_name = #{appName}
        GROUP BY tt2.keyword
    </select>

  <select id="queryChannelList" resultType="com.alibaba.fastjson.JSONObject">
      SELECT
          t1.id,
          channel_code as 'channelCode',
          channel_name as 'channelName',
          t1.url,
          schema_uri as 'schemaUri',
          click_track_url as 'clickTrackUrl',
          actionbar_click_url as 'actionbarClickUrl'
      FROM
          ctop_kuaishou_channel t1
              LEFT JOIN ctop_kuaishou_channel_app_info t2 ON t1.app_id = t2.app_id
      WHERE
          t1.state = 1
        AND t1.channel_state = 1
        AND t1.account_id = #{accountId}
        AND t2.app_name = #{appName}
      GROUP BY   t1.id
      ORDER BY
      t1.create_time DESC
  </select>


     <select id="queryChannelListByAccount" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
      SELECT
          id,
          channel_code
      FROM
          ctop_kuaishou_channel
      WHERE channel_state = 1
        AND state = 1
        AND account_id = #{accountId}
    </select>


    <select id="queryUsedChannel" resultType="cn.com.ctop.kuaishou.modules.channel.entity.KuaishouChannel">
        SELECT t2.*
        FROM ctop_kuaishou_channel_rel t1
                 INNER JOIN ctop_kuaishou_channel t2
                            ON t1.account_id = t2.account_id AND t1.channel_code = t2.channel_code
        WHERE t2.account_id = #{accountId}
          AND t2.usage_level = #{level}
          AND t2.state = 1
          AND t2.channel_state = 1
          AND t1.campaign_id = #{campaignId} LIMIT 1
    </select>

</mapper>