<?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="com.crm.manager.dao.mapper.CustomLoginMarkDeleteMapper">
    <select id="getNotActivateLoginList" resultType="Long">
        select a.login
        from account_view_${platform} a
                 left join custom_login b on a.login = b.login
                 left join custom_login_mark_delete c on a.login = c.login
                 left join (select DISTINCT login from trade_dw) t on a.login = t.login
        where DATE_FORMAT(b.add_time, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(DATE_SUB(now(), interval 30 day), '%Y-%m-%d')
          and a.balance = 0
          and a.equity = 0
          and a.floating = 0
          and a.credit = 0
          and (c.formal_del is null or c.formal_del = 0)
          and t.login is null
          and b.platform = #{platform}
          and b.follow_subscribe_valid !=1
    </select>

    <update id="batchUpdateCustomlogin">
        update custom_login set `del`= #{del},mark_del_time = #{markDelTime}
        where platform = #{platform} and `login` in
        <foreach collection="loginList" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>

    <select id="getPositionLoginList" resultType="Long">
        select a.login
        from custom_login a
                 left join (select DISTINCT login
                            from trade_position_view_${platform}
                            where DATE_FORMAT(open_time, '%Y-%m-%d') <![CDATA[>]]>
                                  DATE_FORMAT(DATE_SUB(now(), interval 3 month), '%Y-%m-%d')) t on a.login = t.login
                 left join custom_login_mark_delete c on a.login = c.login
        where t.login is null
          and (c.formal_del is null or c.formal_del = 0)
          and a.login is not null
          and a.platform = #{platform}
          and a.follow_subscribe_valid !=1
    </select>

    <select id="getPendingLoginList" resultType="Long">
        select a.login
        from custom_login a
                 left join (select DISTINCT login
                            from trade_pending_view_${platform}
                            where DATE_FORMAT(open_time, '%Y-%m-%d') <![CDATA[>]]>
                                  DATE_FORMAT(DATE_SUB(now(), interval 3 month), '%Y-%m-%d')) t on a.login = t.login
                 left join custom_login_mark_delete c on a.login = c.login
        where t.login is null
          and (c.formal_del is null or c.formal_del = 0)
          and a.login is not null
          and a.platform = #{platform}
          and a.follow_subscribe_valid !=1
    </select>

    <select id="getHistoryLoginList" resultType="Long">
        select a.login
        from custom_login a
                 left join(select DISTINCT login
                           from trade_history_${platform} _${month1}
            union select DISTINCT login
                           from trade_history_${platform}_${month2}
                           union
                           select DISTINCT login
                           from trade_history_${platform}_${month3}
                           union
                           select DISTINCT login
                           from trade_history_${platform}_${month4}
                           where
                               DATE_FORMAT(open_time
                               , '%Y-%m-%d') <![CDATA[>]]> DATE_FORMAT(DATE_SUB(now()
                               , interval 3 month)
                               , '%Y-%m-%d')
                              or
                               DATE_FORMAT(close_time
                               , '%Y-%m-%d') <![CDATA[>]]> DATE_FORMAT(DATE_SUB(now()
                               , interval 3 month)
                               , '%Y-%m-%d')) t on a.login = t.login
                 left join custom_login_mark_delete c on a.login = c.login
        where t.login is null
          and (c.formal_del is null or c.formal_del = 0)
          and a.login is not null
          and a.platform = #{platform}
          and a.follow_subscribe_valid !=1
    </select>

    <select id="getDwTradeLoginList" resultType="Long">
        select a.login
        from custom_login a
                 left join trade_dw b on a.login = b.login and DATE_FORMAT(b.close_time, '%Y-%m-%d') <![CDATA[>]]>
                                                               DATE_FORMAT(DATE_SUB(now(), interval 3 month), '%Y-%m-%d')
                 left join custom_login_mark_delete c on a.login = c.login
        where a.platform = #{platform}
          and a.follow_subscribe_valid !=1
        and b.login is null and (c.formal_del is null or c.formal_del = 0) and a.login is not null
    </select>

    <select id="getBalanceLoginList" resultType="Long">
        select cus.login from custom_login cus
        left join account_view_${platform} t on cus.login = t.login
        where (t.balance <![CDATA[<]]> 20 or t.login is null) and cus.login is not null
        and cus.platform = #{platform} and cus.follow_subscribe_valid !=1 and cus.login in
        <foreach collection="loginList" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

    <select id="getNotTradeLoginList" resultType="com.crm.rely.backend.core.pojo.table.CustomLoginMarkDeleteTable">
        select cus.login,cus.platform,t.balance,now() add_time,0 formalDel from custom_login cus
        left join account_view_${platform} t on cus.login = t.login
        where cus.platform = #{platform} and cus.login in
        <foreach collection="loginList" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

    <select id="getCustomLoginMarkDeleteList" resultType="Long">
        select a.login
        from custom_login_mark_delete a
        where formal_del = 0
          and a.platform = #{platform}
    </select>

    <delete id="delCustomloginMarkDelete" parameterType="Long">
        delete from custom_login_mark_delete where platform = #{platform} and login in
        <foreach collection="delMarkList" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </delete>

    <select id="getCustomLoginFormalDeleteList" resultType="Long">
        select a.login
        from custom_login_mark_delete a
        where formal_del = 0
          and platform = #{platform}
          and DATE_FORMAT(a.add_time, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(DATE_SUB(now(), interval 15 day), '%Y-%m-%d')
    </select>

    <update id="batchUpdateCustomloginMarkFormalDel" parameterType="Long">
        update custom_login_mark_delete set `formal_del`= 1, modify_time = now()
        where platform = #{platform} and `login` in
        <foreach collection="formalDelList" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>

    <select id="getCustomloginList"
            resultType="com.crm.rely.backend.core.entity.custom.login.delete.record.CustomLoginDeleteRecordAddEntity">
        select a.c_id,a.custom_id,a.login,a.platform
        from custom_login a where a.platform = #{platform} and login in
        <foreach collection="formalDelList" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

    <select id="getCustomLoginMarkDeleteByLogin"
            resultType="com.crm.rely.backend.core.pojo.table.CustomLoginMarkDeleteTable">
        select *
        from custom_login_mark_delete
        where login = #{login}
          and platform = #{platform}
          and formal_del = 1 limit 1
    </select>

    <select id="getCustomLoginDeleteExclude" resultType="Long">
        select b.login
        from custom_login_delete_exclude a
                 left join custom_login b on a.c_id = b.c_id
        where b.c_id is not null
          and b.platform = #{platform}
        union
        select b.login
        from custom_login_delete_exclude b
        where b.login is not null
          and b.platform = #{platform}
    </select>
</mapper>