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


    <select id="selectCountByAccountIdAndDate" resultType="java.lang.Integer">
        select
        count(1)
        from
        ctop_mail_log
        where account_id = #{accountId}
        and DATE_FORMAT(create_time,'%Y-%m-%d') = #{createTime}
        and mail_type = #{type}


    </select>
    <select id="selectMailList" resultType="java.lang.String">
     select
     t2.email
     from
     ctop_project_member t1
     left join sys_user t2
     on t1.user_id = t2.id
     where t1.project_id = #{projectId}
     and t2.`status` = 1
     and t2.email != ''
    </select>


    <select id="selectWeiXinIdList" resultType="com.alibaba.fastjson.JSONObject">
     select
     t2.wexin_id,t2.corp_id
     from
     ctop_project_member t1
     left join ctop_corp_wexin_user t2
     on t1.user_id = t2.user_id
     LEFT JOIN sys_user t3
     on  t1.user_id = t3.id
     where t1.project_id = #{projectId}
     and t3.`status` = 1
     and t2.wexin_id != ''
    </select>

    <select id="getWChatIdByUserId" resultType="com.alibaba.fastjson.JSONObject">
    select t1.wexin_id,corp_id
    from ctop_corp_wexin_user t1
    left join  sys_user t2
    on  t1.user_id = t2.id
    where  t1.user_id = #{userId}
    and t2.`status` = 1

    </select>


    <select id="getWChatIdByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        select wexin_id, corp_id, wexin_name
        from ctop_corp_wexin_user
        where user_id = (select ctop_user_allocation.user_id
                         from ctop_user_allocation
                         where account_id = #{accountId} limit 1)
        union all
        select wexin_id, corp_id, wexin_name
        from ctop_corp_wexin_user
        where user_id = (select leader_id
                         from sys_user
                         where id = (SELECT ctop_user_allocation.user_id
                                     FROM ctop_user_allocation
                                     WHERE account_id = #{accountId}
            LIMIT 1))
        union all
        select wexin_id, corp_id, wexin_name
        from ctop_corp_wexin_user
        where user_id = 'f6a6843879c949618e4a859140c8a127';

    </select>


    <select id="getIllegalWChatId" resultType="com.alibaba.fastjson.JSONObject">
        select t.*
        from (
                 select wexin_name, wexin_id, corp_id, 1 as 'orders'
                 from ctop_corp_wexin_user
                 where user_id = (select ctop_user_allocation.user_id
                                  from ctop_user_allocation
                                  where account_id = #{accountId} limit 1)
        union all
        select wexin_name, wexin_id, corp_id, 2 as 'orders'
        from ctop_corp_wexin_user
        where user_id = (select leader_id
                         from sys_user
                         where id = (SELECT ctop_user_allocation.user_id
                                     FROM ctop_user_allocation
                                     WHERE account_id = #{accountId}
            LIMIT 1))
        union all
        select wexin_name, wexin_id, corp_id, 3 as 'orders'
        from ctop_corp_wexin_user
        where user_id = 'b07f2bdd0f914f1dab2b4e841764911f')  t
        GROUP BY t.wexin_id
        ORDER BY t.orders
    </select>

    <select id="getUserNameByUserId" resultType="java.lang.String">
    select
    realname
    from sys_user
    where id = #{userId}
    </select>

    <select id="getWeChatByMail" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            wexin_id,corp_id
        FROM
            ctop_corp_wexin_user
        WHERE
            wexin_email = #{mail}
    </select>
    <select id="getAccountInfo" resultType="com.alibaba.fastjson.JSONObject">
        select media_id as 'mediaId', (case
                                           when media_id = 1
                                               then 'b07f2bdd0f914f1dab2b4e841764911f'
                                           when media_id = 2
                                               then 'f6a6843879c949618e4a859140c8a127'
                                           else ''
            end) as 'leaderId'
        from ctop_user_allocation
        where account_id = #{accountId}
    </select>
    <select id="getBytedanceCost" resultType="java.math.BigDecimal">
        select sum(cost)
        from application.bytedance_advertiser_report_daily_dw
        where account_id = #{accountId}
          and stat_datetime >= DATE_FORMAT(CURDATE(), '%Y%m01')

    </select>
    <select id="getKuaishouCost" resultType="java.math.BigDecimal">
        select  sum(charge) from application.kuaishou_account_report_daily_dw where stat_date >= DATE_FORMAT(CURDATE() ,'%Y%m01') and account_id = #{accountId}

    </select>

</mapper>