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

    <select id="getByIdLock" resultType="cn.pluss.platform.entity.MerchantChannelStatus">
        SELECT * FROM tb_pluss_merchant_channel_status WHERE id = #{id} FOR UPDATE;
    </select>

    <select id="getByMerchantCode" resultType="cn.pluss.platform.entity.MerchantChannelStatus">
        SELECT t1.* FROM tb_pluss_merchant_channel_status t1
            LEFT JOIN tb_pluss_merchant_channel t2 ON t1.channel = t2.id
            WHERE t2.`status` = '1' AND t1.merchantCode = #{merchantCode} AND t1.channel = #{channel}
    </select>

    <select id="getRyxNoCertMerchant" resultType="cn.pluss.platform.entity.MerchantChannelStatus">
        SELECT t1.* FROM tb_pluss_merchant_channel_status t1
            LEFT JOIN tb_pluss_sub_merchant_apply_order t2 ON t1.merchantCode = t2.merchantCode
            AND t1.channel = t2.channelId
            WHERE t1.channel = 3 AND t1.`status` = 3 AND (t2.`status` = 0 OR t2.`status` IS NULL OR t2.resultStatus = '审核中' OR t2.resultStatus = '未提交' OR t2.resultStatus = '编辑中')
    </select>

    <select id="selectListWithUserId" resultType="cn.pluss.platform.entity.MerchantChannelStatus">
        SELECT t1.*, mbi.userId FROM tb_pluss_merchant_channel_status t1
            LEFT JOIN tb_pluss_merchant_base_info mbi ON t1.merchantCode = mbi.merchantCode
            ${ew.customSqlSegment}
    </select>

    <select id="getUserNumInfo" resultType="cn.pluss.platform.vo.UserNumVO">
        SELECT
        count(1) as totalNum,
        IFNULL(SUM(IF(t.createDate = #{date},1,0)),0) AS addNum
        FROM (
         SELECT
             merchantCode,
            <if test="dateType == null or dateType == 1">
                DATE_FORMAT(createTime, '%Y-%m-%d' ) AS createDate
            </if>
            <if test="dateType == 2">
                DATE_FORMAT(createTime, '%Y-%m' ) AS createDate
            </if>
            <if test="dateType == 3">
                DATE_FORMAT(createTime, '%Y' ) AS createDate
            </if>
         FROM
             tb_pluss_merchant_channel_status
         WHERE
             `status` = 3
         GROUP BY
             merchantCode
            ) t
         LEFT JOIN tb_pluss_user_app ua ON t.merchantCode = ua.merchantCode
        WHERE ua.userType = 'promoter' AND ua.parentId = #{userId}
    </select>

    <select id="selectListByMerchantCode" resultType="cn.pluss.platform.vo.MerchantChannelStatusVO">
        SELECT mcs.*, smWx.subMchId wxMercNo, smZfb.subMchId zfbMercNo FROM tb_pluss_merchant_channel_status mcs
        LEFT JOIN tb_pluss_sub_merchant smWx ON smWx.merchantId = mcs.merchantId AND smWx.subMchType = 'WX'
        LEFT JOIN tb_pluss_sub_merchant smZfb ON smZfb.merchantId = mcs.merchantId AND smZfb.subMchType = 'ZFB'
        WHERE ((smWx.id IS NULL) OR (smZfb.id IS NULL)
            OR (smWx.subMchId NOT LIKE '%\_%' AND smZfb.subMchId NOT LIKE '%\_%')
            OR RIGHT(smWx.subMchId, 4) = RIGHT(smZfb.subMchId, 4) OR (smWx.channelTypeId != 4 AND smZfb.channelTypeId != 4)) AND mcs.merchantCode IN
        <foreach open="(" separator="," close=")" collection="merchantCodeList" item="item">
            #{item}
        </foreach>
    </select>

    <select id="selectByChangeFlowId" resultType="cn.pluss.platform.entity.MerchantChannelStatus">
        SELECT * FROM tb_pluss_merchant_channel_status
        WHERE channel = 4 AND `status` = 3 AND (extra ->> '$.changeFlowId') = #{changeFlowId}
    </select>

    <select id="selectBySysFlowId" resultType="cn.pluss.platform.entity.MerchantChannelStatus">
        SELECT * FROM tb_pluss_merchant_channel_status
        WHERE channel = 4 AND `status` = 3 AND (extra ->> '$.sysFlowId') = #{sysFlowId}
    </select>

</mapper>
