<?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.pluss.platform.mapper.MerchantRateNewMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.pluss.platform.entity.MerchantRateNew">
        <id column="id" property="id"/>
        <result column="channel" property="channel"/>
        <result column="merchantId" property="merchantId"/>
        <result column="wxRate" property="wxRate"/>
        <result column="aliRate" property="aliRate"/>
        <result column="bankRate" property="bankRate"/>
        <result column="bankRateLarge" property="bankRateLarge"/>
        <result column="createTime" property="createTime"/>
        <result column="updateTime" property="updateTime"/>
    </resultMap>

    <select id="selectByUserId" resultType="cn.pluss.platform.entity.MerchantRateNew">
        SELECT mrn.id,
               mrn.channel,
               mrn.merchantId,
               mrn.wxRate,
               mrn.aliRate,
               mrn.bankRate,
               mrn.bankRateLarge,
               mrn.createTime,
               mrn.updateTime
        FROM tb_pluss_merchant_rate_new mrn
                 LEFT JOIN tb_pluss_merchant_channel_status mcs
                           ON mrn.merchantId = mcs.merchantId AND mrn.channel = mcs.channel
                 LEFT JOIN tb_pluss_merchant_base_info mbi ON mcs.merchantCode = mbi.merchantCode
        WHERE mbi.userId = #{userId}
          AND mrn.channel = #{channel}
    </select>

    <select id="selectByMerchantCode" resultType="cn.pluss.platform.entity.MerchantRateNew">
        SELECT mrn.id,
               mrn.channel,
               mrn.merchantId,
               mrn.wxRate,
               mrn.aliRate,
               mrn.bankRate,
               mrn.bankRateLarge,
               mrn.createTime,
               mrn.updateTime
        FROM tb_pluss_merchant_rate_new mrn
                 LEFT JOIN tb_pluss_merchant_channel_status mcs
                           ON mrn.merchantId = mcs.merchantId AND mrn.channel = mcs.channel
        WHERE mcs.merchantCode = #{merchantCode}
          AND mrn.channel = #{channel}
    </select>

    <select id="pageData" resultType="java.util.Map">
        SELECT mbi.userId,
        mbi.alias,
        mbi.merchantCode,
        mcs2.merchantId,
        IF(mcs.`status` IS NULL, '-', IFNULL(mrn.wxRate, 38)) wxRateD1,
        IF(mcs.`status` IS NULL, '-', IFNULL(mrn.aliRate, 38)) aliRateD1,
        IF(mcs.`status` IS NULL, '-', IFNULL(mrn.bankRate, 36)) bankRateD1,
        IF(mcs.`status` IS NULL OR mcs.channel = '1', '-', IFNULL(mrn.bankRateLarge, 35)) bankRateLargeD1,
        IF(mcs2.`status` IS NULL, '-', IFNULL(mrn2.wxRate, 38)) wxRateD0,
        IF(mcs2.`status` IS NULL, '-', IFNULL(mrn2.aliRate, 38)) aliRateD0,
        IF(mcs2.`status` IS NULL, '-', IFNULL(mrn2.bankRate, 36)) bankRateD0,
        IF(mcs2.`status` IS NULL, '-', IFNULL(mrn2.bankRateLarge, 35)) bankRateLargeD0,
        mrnr.wxRate wxRateEditD0,
        mrnr.aliRate aliRateEditD0,
        mrnr.bankRate bankRateEditD0,
        mrnr.bankRateLarge bankRateLargeEditD0,
        IFNULL(mrnr.createTime, mcs.updateTime) createTime,
        IF(mcs2.`status` IS NULL, '-1', IFNULL(mrnr.`status`, '1')) status
        FROM tb_pluss_merchant_base_info mbi
        LEFT JOIN tb_pluss_merchant_channel_status mcs
        ON mbi.merchantCode = mcs.merchantCode AND mcs.channel IN (1, 3)
        LEFT JOIN tb_pluss_merchant_rate_new mrn
        ON mrn.channel = mcs.channel AND mrn.merchantId = mcs.merchantId AND
        mrn.channel IN (1, 3) AND mcs.channel IN (1, 3)
        LEFT JOIN tb_pluss_merchant_channel_status mcs2
        ON mbi.merchantCode = mcs2.merchantCode AND mcs2.channel = 4
        LEFT JOIN tb_pluss_merchant_rate_new mrn2
        ON mrn2.merchantId = mcs2.merchantId AND mrn2.channel = mcs2.channel AND mrn2.channel = 4 AND
        mcs2.channel = 4
        LEFT JOIN tb_pluss_merchant_rate_new_record mrnr ON mrnr.merchantId = mcs2.merchantId
        AND mrnr.channel = mcs2.channel AND mrnr.`status` = 0 AND mrnr.channel = 4 AND mcs2.applicationId IS NULL
        <where>
            <if test="condition.status != null and condition.status != ''">
                AND mrnr.status = #{condition.status}
            </if>

            <if test="condition.userId != null and condition.userId != ''">
                AND mbi.userId = #{condition.userId}
            </if>
        </where>

        ORDER BY mrnr.updateTime DESC
    </select>

    <select id="pageTempSxfRateNoChange" resultType="cn.pluss.platform.entity.MerchantRateNew">
        SELECT DISTINCT 1 channel, mbi.userId, mcs.merchantId, IFNULL(mr.rate, 38) wxRate, IFNULL(mr.rate, 38) aliRate, IFNULL(mr.rate, 38) bankRate, -1 bankRateLarge FROM tb_pluss_merchant_base_info mbi
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON mbi.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_rate mr ON mbi.userId = mr.userId
            LEFT JOIN tb_pluss_rate_operation_temp rot ON rot.channel = mcs.channel AND rot.merchantId = mcs.merchantId
        WHERE mcs.channel = 1 AND mcs.`status` = 3 AND rot.id IS NULL
    </select>

    <select id="pageTempRyxRateNoChange" resultType="cn.pluss.platform.entity.MerchantRateNew">
        SELECT DISTINCT 3 channel, mbi.userId, mcs.merchantId, IFNULL(mr.rate, 38) wxRate, IFNULL(mr.rate, 38) aliRate, IFNULL(mr.rate, 38) bankRate, IFNULL(mr.rate, 38) bankRateLarge FROM tb_pluss_merchant_base_info mbi
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON mbi.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_rate mr ON mbi.userId = mr.userId
            LEFT JOIN tb_pluss_rate_operation_temp rot ON rot.channel = mcs.channel AND rot.merchantId = mcs.merchantId
        WHERE mcs.channel = 3 AND mcs.`status` = 3 AND rot.id IS NULL
    </select>

    <select id="pageTempYsRateNoChange" resultType="cn.pluss.platform.entity.MerchantRateNew">
        SELECT DISTINCT 4 channel, mbi.userId, mcs.merchantId, IFNULL(mr.rate, 38) wxRate, IFNULL(mr.rate, 38) aliRate, IFNULL(mr.rate, 38) bankRate, IFNULL(mr.rate, 38) bankRateLarge FROM tb_pluss_merchant_base_info mbi
            LEFT JOIN tb_pluss_merchant_channel_status mcs ON mbi.merchantCode = mcs.merchantCode
            LEFT JOIN tb_pluss_merchant_rate mr ON mbi.userId = mr.userId
            LEFT JOIN tb_pluss_rate_operation_temp rot ON rot.channel = mcs.channel AND rot.merchantId = mcs.merchantId
        WHERE mcs.channel = 4 AND mcs.`status` = 3 AND rot.id IS NULL
    </select>
</mapper>
