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

    <select id="selectAccountListByProjectId" resultType="cn.com.ctop.common.module.entity.UserAllocation">
        select
        a.user_id as userId,
        a.user_name as userName,
        a.auth_name as authName,
        a.account_id as accountId
        from
        ctop_user_allocation a
        left join ctop_project b on a.project_id = b.id
        where b.id = #{projectId}
        and account_status = 0
    </select>
    <select id="getAccountListByUserIdAndProductId" resultType="cn.com.ctop.common.module.entity.UserAllocation">
        select cua.account_id, cua.auth_name, cua.user_name
        from ctop_user_allocation cua
        left join ctop_project cp on cua.project_id = cp.id
        where
        cua.project_id = #{projectId}
        and
        cp.product_id=#{productId}
        <if test="userId != null and userId != ''">
            and cua.user_id  =   #{userId}
        </if>
         <if test="authName != null and authName != ''">
            and cua.auth_name like concat(concat('%',#{authName}),'%')
        </if>
        and account_status = 0
    </select>

    <select id="queryAdvertiserId" resultType="String">
        select
            distinct advertiser_id
        from
            ctop_user_allocation
        where account_id=#{accountId}
    </select>
    <select id="queryUserIdByAccountId" resultType="String">
        select
            distinct user_id
        from
            ctop_user_allocation
        where account_id=#{accountId}
    </select>

    <select id="getAccountListByProject" resultType="com.alibaba.fastjson.JSONObject">
       select
       t1.account_id as 'accountId',
       t1.user_name as 'userName',
       t1.auth_name as 'authName',
       t1.account_status as 'accountStatus'
       from ctop_user_allocation t1
       where t1.project_id = #{projectId}
       order by t1.create_time desc
    </select>

    <select id="getAccountListByProjectList" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.project_id as 'projectId',
        t1.account_id as 'accountId',
        t2.realname as 'userName',
        t1.auth_name as 'authName',
        t1.account_status as 'accountStatus'
        from ctop_user_allocation t1
        left join sys_user t2 on t2.id = t1.user_id
        where t1.project_id in
        <foreach collection="list" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>


        order by t1.create_time desc
    </select>


    <select id="getUserIdListByProjectId" resultType="com.alibaba.fastjson.JSONObject">
   SELECT t1.user_id as 'userId',
    t1.account_id as 'accountId',
    t1.user_name  as 'userName'
    from  ctop_kuaishou_advertiser_base_info t1  LEFT JOIN
    ctop_user_allocation  t2
    ON t1.account_id = t2.account_id
    WHERE t2.project_id = #{projectId}
    AND t2.account_status = 0
    GROUP BY t1.user_id;
    </select>
    <select id="getUserIdListByProductId" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
	*
FROM
	(
		SELECT
			t4.user_id AS 'userId',
			t3.account_id AS 'accountId',
			t3.auth_name AS 'userName',
			t1.project_name AS 'projectName'
		FROM
			ctop_project t1
		INNER JOIN (
			SELECT
				project_id
			FROM
				ctop_project_member
			WHERE
				project_id IN (
					SELECT
						id
					FROM
						ctop_project
					WHERE
						product_id = #{productId}
					AND media_id IN (2, 4)
				)
        <if test="userId != null and userId != ''">
            AND user_id = #{userId}
        </if>
		) t2 ON t1.id = t2.project_id
		LEFT JOIN ctop_user_allocation t3 ON t1.id = t3.project_id
		LEFT JOIN ctop_kuaishou_advertiser_base_info t4 ON t3.account_id = t4.account_id
		WHERE
			t1.product_id = #{productId}
		AND t3.account_status = 0
		GROUP BY
			t4.user_id
	) t
ORDER BY
	t.projectName,
	t.userId
    </select>

    <select id="getValidProjectIds" resultType="java.lang.Long">
        SELECT project_id FROM ctop_user_allocation t1
        where account_status = 0
        <if test="mediaId != null">
            AND media_id = #{mediaId}
        </if>
        GROUP BY project_id
    </select>


    <select id="getAccountIdListByUserId" resultType="com.alibaba.fastjson.JSONObject">
    SELECT
    t1.account_id as 'accountId',
    t1.auth_name  as 'authName',
    t1.user_name as 'userName'
    from ctop_user_allocation   t1  LEFT JOIN
    ctop_kuaishou_advertiser_base_info t2
    ON t1.account_id = t2.account_id
    WHERE t2.user_id = #{userId}
    AND t1.account_status = 0
    </select>

    <select id="getAccountIdsByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            allocation.account_id,
            allocation.auth_name,
            project.project_name as project_name
        FROM
            ctop_user_allocation allocation
            left join ctop_project project on allocation.project_id = project.id
        WHERE
            allocation.user_id = #{userId}
          and allocation.account_status=0
    </select>


    <select id="getAllAccountIdsByMediaId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            account_id,
            auth_name,
            project_name
        FROM
            ctop_user_allocation
        WHERE
            media_id=#{mediaId}
          and account_status=0
    </select>

    <select id="getProjectIdByAccountId" resultType="java.lang.Long">
    select  project_id  from  ctop_user_allocation
    where account_id = #{accountId}
    limit 1
    </select>

    <select id="getUserAllocation" resultType="cn.com.ctop.common.module.entity.UserAllocation">
    select  *  from  ctop_user_allocation
    where account_id=#{accountId}
    limit 1
    </select>

    <select id="getSwitchStatusByAccount" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            monitoring_link,
            landing_page
        FROM
            ctop_user_allocation
        WHERE
            account_id = #{accountId}
    </select>

    <select id="queryAccountsByDeleteComment" resultType="java.lang.Long">
        SELECT
            account_id
        FROM
            ctop_user_allocation
        WHERE
            media_id = 2
          AND account_status = 0
          AND delete_comment = 0
    </select>
    <select id="getCompanyNameByUserId" resultType="java.lang.String">
        select company_name from user_company where user_id = #{planId} limit 1
    </select>
    <select id="listByMediaId" resultType="cn.com.ctop.common.module.entity.UserAllocation">
        select a.* from ctop_user_allocation a
        left join ctop_project p on p.id = a.project_id
        where
        1=1
        <if test="null!=accountStatus">
            and a.account_status = #{accountStatus}
        </if>
        <if test="null!=mediaId">
            and p.media_id = #{mediaId}
        </if>
    </select>
    <select id="getByAccountIds" resultType="com.alibaba.fastjson.JSONObject">
        select
        account_id as 'accountId',
        auth_name as 'authName'
        from ctop_user_allocation where
        account_id in
        <foreach collection="accountIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>


    </select>
    <select id="getKuaiShouUserIdListByProjectId" resultType="java.lang.Long">
        select t2.user_id from ctop_user_allocation t1
        left join  ctop_kuaishou_advertiser_base_info t2
        on t1.account_id = t2.account_id
        where t1.project_id = #{projectId}
        group by t2.user_id
    </select>
    <select id="selectAccountsByKuaishouUserId" resultType="com.alibaba.fastjson.JSONObject">
    SELECT
	t2.account_id AS 'accountId',
	t2.user_name AS 'userName',
	t2.auth_name AS 'authName',
	t2.account_status AS 'accountStatus'
    FROM
	ctop_kuaishou_advertiser_base_info t1
    LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
    WHERE
	t1.user_id = #{userId}
    AND t2.id IS NOT NULL;

    </select>

    <update id="updateProjectStrategyStatus">
        update ctop_user_allocation set auto_delivery = #{projectStrategy}
        where account_id = #{accountId}
    </update>

    <select id="queryAutomaticAccounts" resultType="java.lang.Long">
        select account_id from ctop_user_allocation
        where project_id = #{projectId}
        AND auto_delivery = 1
    </select>

    <!-- 根据账户id 查看财务账户 -->
    <select id="getCwjsSettlementByAccountId" resultType="java.util.LinkedHashMap">
        select * from ctop_cwjs_settlement_info
        WHERE del_flag = 0
          and account_id = #{accountId}
    </select>



    <select id="getAccountInfoBySalesId" resultType="cn.com.ctop.common.module.entity.UserAllocation">
        select cua.*
            from ctop_user_allocation cua
            left join ctop_project cp on cp.id = cua.project_id
        where 1=1
          and cua.media_id = #{mediaId}
          <if test="advertiserId!=null and advertiserId!= '' ">
              and cp.advertiser_id = #{advertiserId}
          </if>
        <if test="productId!=null and productId!= '' ">
            and cp.product_id = #{productId}
        </if>
        <if test="projectId!=null and projectId!= '' ">
            and cp.id = #{projectId}
        </if>
        <if test="sales !=null and sales.size() > 0">
            AND cp.sale_id in
            <foreach collection="sales" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>
    <select id="getAccountListByProjectListV2" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.project_id as 'projectId',
        t1.account_id as 'accountId',
        (select realname from sys_user where id = t1.user_id ) as 'userName',
        t1.auth_name as 'authName',
        t1.account_status as 'accountStatus'
        from ctop_user_allocation t1
        where t1.project_id in
        <foreach collection="list" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        and t1.account_status = 0
        order by t1.create_time desc
    </select>
    <select id="getAccountListByProjectAndUserId" resultType="com.alibaba.fastjson.JSONObject">
        select
       t1.account_id as 'accountId',
       t1.user_name as 'userName',
       t1.auth_name as 'authName',
       t1.account_status as 'accountStatus'
       from ctop_user_allocation t1
       where t1.project_id = #{projectId}
        <if test="userId !=null and userId != '' ">
            and t1.user_id = #{userId}
        </if>
       order by t1.create_time desc


    </select>
    <!-- 修改状态 -->
    <update id="updCwjsSettlementStatus">
        UPDATE ctop_cwjs_settlement_info
        SET STATUS = #{status}
        WHERE
            id = #{id}
    </update>

    <select id="getAccountListByProjectId" resultType="java.lang.Long">
        select account_id from ctop_user_allocation
        WHERE account_status = 0
          AND project_id = #{projectId}
    </select>


    <select id="getProductInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id,product_name  FROM ctop_product
        WHERE id = (
        SELECT product_id FROM ctop_project
        WHERE id = (
            SELECT project_id FROM ctop_user_allocation
            WHERE account_id = #{accountId}
        )
        )

    </select>

    <select id="getAccountInfoByProjectId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t1.account_id as 'accountId',
            t1.auth_name as 'accountName',
            t1.project_id as 'projectId',
            t2.project_name as 'projectName'
        FROM ctop_user_allocation t1
                 LEFT JOIN ctop_project t2 ON t1.project_id=t2.id
        WHERE t1.project_id = #{projectId}
        AND t1.account_status = 0
    </select>

    <select id="getAccountInfoByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.user_id,
               t1.account_id as 'accountId',
               t1.auth_name as 'accountName',
               t1.project_id as 'projectId',
               t2.project_name as 'projectName'
        FROM ctop_user_allocation t1
                 LEFT JOIN ctop_project t2 ON t1.project_id = t2.id
        WHERE t1.account_id = #{accountId}
    </select>


</mapper>
