<?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.m2pool.manage.mapper.ManageBroadcastMapper">


    <select id="selectUserListByPage" resultType="com.m2pool.manage.dto.ManageUserDto">
        SELECT
            uma.id,
            uma.user as `user`,
            uma.miner_user as minerUser,
            uma.coin,
            uab.balance,
            uab.active,
            COALESCE(uab.amount,0) as amount,
            uma.status
        FROM
            user_account_balance uab
                LEFT JOIN user_miner_account uma ON uab.ma_id = uma.id
        <where>
            uma.coin = #{coin} AND uma.status = 0 AND uab.status = 0
            <if test="user != null and user != ''">
                AND uma.user like CONCAT('%',#{user},'%')
            </if>
            <if test="minerUser != null and minerUser != ''">
                AND uma.miner_user like CONCAT('%',#{minerUser},'%')
            </if>
        </where>
    </select>
    <select id="getUserInfo" resultType="com.m2pool.manage.dto.ManageUserInfoDto">
        SELECT
            id,
            coin,
            `user`,
            address,
            `date_in` as `date`,
            COALESCE(DATE(`should_out_date`),DATE(`date_in`),DATE(`date_out`))  as shouldOutDate,
            max_height,
            allocation_amount as allocationAmount,
            transfer_amount as transferAmount
        FROM
            manage_wallet_out_in
        <where>
            coin = #{coin} AND `user` = #{user}
            <choose>
                <when test="startDate != null and endDate != null">
                    and DATE(`date_in`) >= #{startDate} and DATE(`date_in`) <![CDATA[ <= ]]>  #{endDate}
                </when>
                 <otherwise>
                    and `date_in` >= DATE_SUB(DATE(NOW()), INTERVAL 1 MONTH)
                </otherwise>
            </choose>
        </where>
        order by `shouldOutDate` desc
    </select>
    <select id="getHistoryBalance" resultType="com.m2pool.manage.dto.HistoryBalanceDto">
        SELECT
            uab.balance
        FROM
            user_account_balance uab
                LEFT JOIN user_miner_account uma ON uab.ma_id = uma.id
        WHERE
            uma.coin = #{coin} AND uma.miner_user=#{user}
        order by uab.status desc
    </select>
    <select id="getMiningUserInfo" resultType="com.m2pool.manage.dto.ManageMiningUserDto">
        SELECT
            coin,
            `user`,
            miner_user as minerUser
        FROM
            user_miner_account
        WHERE
          `user` = #{user} and status = 0
    </select>
    <select id="getMiningPowerInfo" resultType="com.m2pool.manage.dto.ManageMiningUserPowerDto">
      SELECT
            `user` as minerUser,
            `date`,
             mhs as pv
        FROM
            ${coin}_users_30m
        <where>
            `user` = #{minerUser}
            <choose>
                <when test="startDate != null and endDate != null">
                    and `date` >= #{startDate} and `date`<![CDATA[ <= ]]>  #{endDate}
                </when>
                <otherwise>
                    and `date` >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
                </otherwise>
            </choose>

        </where>

    </select>
    <select id="getMinerUserOnlineStatus" resultType="com.m2pool.manage.dto.ManageMiningUserOnlineDto">
        select
            `date`,
            sum(case when `state` = "online" then 1 else 0 end) as onlineNum,
            sum(case when `state` = "offline" then 1 else 0 end) as offlineNum
        from
            ${coin}_mhs30m
        <where>
            `user` = #{minerUser}
            <choose>
                <when test="startDate != null and endDate != null">
                    and `date` >= #{startDate} and `date`<![CDATA[ <= ]]>  #{endDate}
                </when>
                <otherwise>
                    and `date` >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
                </otherwise>
            </choose>
        </where>
        group by `date`
    </select>
    <select id="getAllMinerUserByCoin" resultType="java.lang.String">
        select miner_user from  user_miner_account where coin = #{coin} and `user` = #{user} and status = 0;
    </select>
    <select id="getListDataByPage" resultType="com.m2pool.manage.dto.ManageBroadcastDto">
        SELECT
            id,
            content,
            create_user as createUser,
            update_user as updateUser,
            update_time as updateTime,
            create_time as createTime,
            button_content as buttonContent,
            button_path as buttonPath
        FROM
            manage_broadcast
        where del =false
    </select>
    <select id="getAccountIncome" resultType="java.math.BigDecimal">
        SELECT
            COALESCE(SUM(amount), 0)
        FROM
        wallet_in
        where
            coin = #{coin} AND `user` = #{user} and state = 1
    </select>
    <select id="getAccountExpend" resultType="java.math.BigDecimal">
        SELECT
            COALESCE(SUM(amount), 0)
        FROM
            wallet_outv2
        where
            coin = #{coin} AND `user` = #{user}
    </select>
    <select id="selectNeedDeleteNumbers" resultType="java.lang.Integer">
        select count(*) from ${coin}_mhs30m where `date` <![CDATA[ <= ]]> #{date}
    </select>


    <delete id="deleteOnlineAndOfflineData">
        delete from ${coin}_mhs30m where `date` <![CDATA[ <= ]]> #{date} LIMIT 5000
    </delete>

    <select id="getStartPayments" resultType="com.m2pool.manage.dto.SummaryOfPendingPaymentsDto">
        SELECT
            uma.miner_user AS `user`,
            uma.coin,
            COALESCE ( uab.amount, 0 ) as startPayAmount
        FROM
            user_account_balance uab
                JOIN user_miner_account uma ON uma.id = uab.ma_id
        WHERE
        uab.status = 0   AND (
        <foreach collection="list" item="item" separator="OR">
            (`miner_user` = #{item.user} AND coin = #{item.coin})
        </foreach>)
    </select>

</mapper>
