fix otc

Helius

2022-03-09 8f07f23b122218dd8679c43d0f9d0c73f76685bc

fix otc

 src/main/resources/mapper/otc/OtcBlackListDao.xml

New file
@@ -0,0 +1,19 @@
<?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.xcong.excoin.modules.otc.dao.OtcBlackListDao">

    <select id="selectByMemberIdAndBlackMemberId" resultType="com.xcong.excoin.modules.otc.entity.OtcBlackList">
        select * from otc_black_list
        where member_id=#{memberId} and black_member_id=#{blackMemberId}
    </select>

    <select id="selectBlackListInPage" resultType="com.xcong.excoin.modules.otc.vo.BlackListVo">
        select a.id, a.create_time time, b.name from otc_black_list a, member b
        where a.black_member_id=b.id and a.member_id=#{memberId}
    </select>

    <select id="selectBlackListByMemberId" resultType="com.xcong.excoin.modules.otc.entity.OtcBlackList">
        select * from otc_black_list
        where member_id=#{memberId}
    </select>
</mapper>

 src/main/resources/mapper/otc/OtcEntrustOrderDao.xml

New file
@@ -0,0 +1,87 @@
<?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.xcong.excoin.modules.otc.dao.OtcEntrustOrderDao">

    <select id="selectEntrustListInPage" resultType="com.xcong.excoin.modules.otc.vo.EntrustListVo">
        select
            a.id,
            b.id mbId
            ,c.name nickname
            ,a.unit_price unitPrice
            ,a.remain_coin_amount amount
            ,a.limit_min_amount min
            ,a.limit_max_amount max
            ,case when a.order_type = 'B' then b.buy_order_cnt
                  when a.order_type = 'S' then b.sale_order_cnt end orderCnt
            ,case when a.order_type = 'B' then b.finish_ratio
                  when a.order_type = 'S' then b.sale_finish_ratio end finishRatio
            ,d.payment_type payType
        from otc_entrust_order a
        left join otc_market_bussiness b on a.member_id=b.member_id
        left join member c on a.member_id=c.id
        left join member_payment_method d on a.member_id=d.member_id and d.is_defualt=1
<!--        inner join otc_black_list e on a.member_id!=e.black_member_id-->
<!--            <if test="record.memberId != null">-->
<!--                and e.member_id=#{record.memberId}-->
<!--            </if>-->
        <where>
            a.status=1 and a.remain_coin_amount > 0
            <if test="record != null">
                <if test="record.type != null and record.type!=''">
                    and order_type = #{record.type}
                </if>
            </if>
            and a.member_id not in (select black_member_id from otc_black_list x where x.member_id=#{record.memberId})
        </where>
        order by a.unit_price desc
    </select>

    <select id="selectEntrustOrderByOrderType" resultType="com.xcong.excoin.modules.otc.entity.OtcEntrustOrder">
        select a.*, b.payment_type payType
        from otc_entrust_order a
        left join member_payment_method b on a.member_id=b.member_id and b.is_defualt=1
        <where>
            <if test="record.orderType != null and record.orderType != ''" >
               and a.order_type = #{record.orderType}
            </if>
            <if test="record.memberId != null">
                and a.member_id = #{record.memberId}
            </if>
            <if test="record.status != null and record.status == 3">
                and a.status != #{record.status}
            </if>
            <if test="record.status != null and record.status != 3">
                and a.status = #{record.status}
            </if>

            <if test="record.isDefualt != null">
                and b.is_defualt = #{record.isDefualt}
            </if>
        </where>
    </select>

    <select id="selectOwnEntrustListInPage" resultType="com.xcong.excoin.modules.otc.entity.OtcEntrustOrder">
        select * from otc_entrust_order
        <where>
            <if test="record.orderType != null and record.orderType != ''" >
                and order_type = #{record.orderType}
            </if>
            <if test="record.memberId != null">
                and member_id = #{record.memberId}
            </if>
            <if test="record.status != null and record.status == 3">
                and status != #{record.status}
            </if>
            <if test="record.status != null and record.status != 3">
                and status = #{record.status}
            </if>
        </where>
        order by create_time desc
    </select>

    <update id="updateRemainAmount">
        update otc_entrust_order
        set remain_coin_amount = remain_coin_amount + ${amount}
        where id=#{id}
    </update>
</mapper>

 src/main/resources/mapper/otc/OtcMarketBussinessMapper.xml

New file
@@ -0,0 +1,9 @@
<?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.xcong.excoin.modules.otc.dao.OtcMarketBussinessDao">


    <select id="selectMarketBussinessByMemberId" resultType="com.xcong.excoin.modules.otc.entity.OtcMarketBussiness">
        select * from otc_market_bussiness where member_id=#{memberId}
    </select>
</mapper>

 src/main/resources/mapper/otc/OtcMsgHistoryDao.xml

New file
@@ -0,0 +1,30 @@
<?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.xcong.excoin.modules.otc.dao.OtcMsgHistoryDao">


    <select id="getChatBoxMsgList" resultType="com.xcong.excoin.modules.otc.vo.ChatBoxVo">
        select
        a.create_time createTime,
        a.msg msg,
        a.target_id targetId,
        a.member_id memberId,
        a.from_member_id fromMemberId,
        a.is_self isSelf,
        a.msg_type msgType
        from otc_msg_history a
        <where>
            <if test="record!=null">
                a.member_id = ${record.memberId}
                and (
                    (a.from_member_id = ${record.memberId} and a.target_id = ${record.targetId})
                    or
                    (a.from_member_id = ${record.targetId} and a.target_id = ${record.memberId})
                )
            </if>
        </where>
        order by a.create_time asc
    </select>


</mapper>

 src/main/resources/mapper/otc/OtcMsgUserListDao.xml

New file
@@ -0,0 +1,42 @@
<?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.xcong.excoin.modules.otc.dao.OtcMsgUserListDao">

    <select id="getMsgList" resultType="com.xcong.excoin.modules.otc.vo.MsgListVo">
        select
               a.id id,
               a.is_read isRead,
               a.target_id targetId,
               a.member_id memberId,
               a.last_msg_time lastMsgTime,
               b.name nickname
        from otc_msg_user_list a
        left join member b on a.target_id = b.id
        <where>
            <if test="record!=null">
                    a.member_id = #{record.memberId}
                <if test="record.nickname != null and record.nickname != ''">
                    and b.name like concat('%',  #{record.nickname},'%')
                </if>
            </if>
        </where>
        order by a.create_time desc
    </select>

    <select id="selectListByMemberIdAndTargetId" resultType="com.xcong.excoin.modules.otc.entity.OtcMsgUserListEntity">
        select
              *
        from otc_msg_user_list a
        <where>
                    a.member_id = #{memberId} and a.target_id = #{targetId}
        </where>
        order by a.create_time desc
    </select>

    <select id="selectChatListByToAndFrom" resultType="com.xcong.excoin.modules.otc.entity.OtcMsgUserListEntity">
        select * from otc_msg_user_list
        where member_id=#{from} and target_id=#{to}
    </select>


</mapper>

 src/main/resources/mapper/otc/OtcOrderAppealDao.xml

New file
@@ -0,0 +1,5 @@
<?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.xcong.excoin.modules.otc.dao.OtcOrderAppealDao">

</mapper>

 src/main/resources/mapper/otc/OtcOrderDao.xml

New file
@@ -0,0 +1,153 @@
<?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.xcong.excoin.modules.otc.dao.OtcOrderDao">

    <select id="selectOrderListUnFinish" resultType="com.xcong.excoin.modules.otc.entity.OtcOrder">
        select * from otc_order
        where status in (1,2) and member_id=#{memberId} and entrust_order_id=#{entrustOrderId}
    </select>


    <select id="selectOrdderListInPage" resultType="com.xcong.excoin.modules.otc.vo.OrderListVo">
        select
            a.id,
            a.order_no orderNo,
            a.unit_price unitPrice,
            a.coin_amount amount,
            a.status status,
            a.total_amount totalAmount,
            a.create_time creatTime,
            a.order_type orderType,
            b.name name
        from otc_order a
        inner join member b on a.opposite_member_id=b.id
        <where>
            <if test="record!=null">
                <if test="record.status != null and record.status == 1">
                    and a.status in (1, 2)
                </if>
                <if test="record.status != null and record.status == 2">
                    and a.status = 3
                </if>
                <if test="record.status != null and record.status == 3">
                    and a.status = 4
                </if>
                <if test="record.memberId != null">
                    and a.member_id = ${record.memberId}
                </if>
            </if>
        </where>
        order by a.create_time desc
    </select>

    <update id="updateOrderStatusByOrderNo">
        update otc_order
        set status=#{status}
        <if test="payName != null and payName != ''">
            , pay_name = #{payName}
        </if>
        where order_no=#{orderNo}
    </update>

    <select id="selectOrderByOrderNoAndType" resultType="com.xcong.excoin.modules.otc.entity.OtcOrder">
        select * from otc_order
        where order_no=#{orderNo} and order_type=#{orderType}
    </select>

    <select id="selectOrderListWithStatusAndType" resultType="com.xcong.excoin.modules.otc.entity.OtcOrder">
        select * from otc_order
        where order_type=#{type} and status=#{status}
    </select>

    <select id="selectMemberCntForEntrust" resultType="java.lang.Integer">
        select count(distinct member_id) from otc_order
        where entrust_member_id=#{entrustMemberId} and status != 4 and entrust_member_id!=member_id
    </select>

    <select id="selectTotalOrderCount" resultType="java.lang.Integer">
        select count(1) from otc_order
        where member_id=#{entrustMemberId}
        <if test="status != null">
            and status = #{status}
        </if>
        <if test="type != null and type!=''">
            and order_type = #{type}
        </if>
    </select>

    <select id="selectMemberAvgPayTime" resultType="java.math.BigDecimal">
        select
               IFNULL(sum(timestampdiff(SECOND, create_time, pay_time))/count(1), 0)
        from otc_order
        where status=3 and member_id=#{memberId} and order_type='B'
    </select>

    <select id="selectMemberAvgCoinTime" resultType="java.math.BigDecimal">
        select
            IFNULL(sum(timestampdiff(SECOND, pay_time, finish_time))/count(1), 0)
        from otc_order
        where status=3 and member_id=40 and order_type='S'
    </select>

    <select id="selectOrderListForUser" resultType="com.xcong.excoin.modules.otc.entity.OtcOrder">
        select * from otc_order
        where member_id=#{memberId}
        <!-- 查询除完成以外所有订单 -->
        <if test="status != null">
            and status = #{status}
        </if>
    </select>

    <select id="selectOrderTotalAmount" resultType="java.math.BigDecimal">
        select sum(total_amount) from otc_order
        where member_id=#{memberId} and status = 3
    </select>

    <select id="selectByMemberIdAndTargetId" resultType="com.xcong.excoin.modules.otc.vo.ChatOrderVo">
        select
            order_no orderNo,
            member_id memberId,
            unit_price unitPrice,
            coin_amount usdtAmount,
            total_amount totalAmount,
            status status,
            order_type orderType
        from otc_order
        where ((member_id=#{memberId} and opposite_member_id=#{targetId})
            or
              (member_id=#{targetId} and opposite_member_id=#{memberId}))
            and status in (1,2)
        order by create_time desc

    </select>

    <select id="selectOneByMemberIdAndTargetId" resultType="com.xcong.excoin.modules.otc.vo.ChatOrderVo">
        select
            order_no orderNo,
            member_id memberId,
            unit_price unitPrice,
            coin_amount usdtAmount,
            total_amount totalAmount,
            status status,
            order_type orderType
        from otc_order
        where ((member_id=#{memberId} and opposite_member_id=#{targetId})
            or
               (member_id=#{targetId} and opposite_member_id=#{memberId}))
        order by create_time desc
            LIMIT 2

    </select>

    <update id="updateIsReturnByOrderNo">
        update otc_order
        set is_return=#{isReturn}
        where order_no=#{orderNo}
    </update>

    <select id="selectOrderUnEntrust" resultType="com.xcong.excoin.modules.otc.entity.OtcOrder">
        select * from otc_order
        where order_no=#{orderNo} and member_id!=entrust_member_id
    </select>

</mapper>

 src/main/resources/mapper/otc/OtcReturnMoneyDao.xml

New file
@@ -0,0 +1,24 @@
<?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.xcong.excoin.modules.otc.dao.OtcReturnMoneyDao">

    <select id="selectMyTeamAmountInPage" resultType="com.xcong.excoin.modules.otc.vo.TeamVo">
        select
            substring(invite_id, -4) inviteId,
            case when a.referer_id = #{inviteId} then 1
                 else 2 end level,
            (select count(1) from member c where find_in_set(a.invite_id, SUBSTRING_INDEX(c.referer_ids, ',', 3))) cnt,
            sum(IFNULL(b.amount, 0)) amount
        from member a
                 left join otc_return_money b on b.from_member_id=a.id and b.to_member_id=#{memberId}
        where find_in_set(#{inviteId}, SUBSTRING_INDEX(referer_ids, ',', 3))
        group by a.id
        order by amount desc
    </select>

    <select id="selectTotalAmount" resultType="java.math.BigDecimal">
        select IFNULL(sum(amount), 0)
        from otc_return_money
        where to_member_id=#{memberId}
    </select>
</mapper>

 src/main/resources/mapper/otc/OtcSettingDao.xml

New file
@@ -0,0 +1,5 @@
<?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.xcong.excoin.modules.otc.dao.OtcSettingDao">

</mapper>