<?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.kxs.product.biz.mapper.KxsMerchantMapper">

    <resultMap type="com.kxs.product.api.model.KxsMerchant" id="KxsMerchantMap">
        <result column="id" property="id" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <result column="del_flag" property="delFlag" />
        <result column="version" property="version" />
        <result column="merchant_no" property="merchantNo" />
        <result column="merchant_name" property="merchantName" />
        <result column="merchant_card" property="merchantCard" />
        <result column="machine_id" property="machineId" />
        <result column="user_id" property="userId" />
        <result column="merchant_phone" property="merchantPhone" />
        <result column="brand_id" property="brandId" />
        <result column="status" property="status" />
        <result column="address" property="address" />
        <result column="is_maker" property="isMaker" />
        <result column="maker_user_id" property="makerUserId" />
        <result column="standard_status" property="standardStatus" />
        <result column="standard_months" property="standardMonths" />
        <result column="total_amt" property="totalAmt" />
        <result column="remark" property="remark" />

    </resultMap>
    <select id="merchantList" resultType="com.kxs.product.api.vo.kxsapp.merchant.MerchantListVO">
        SELECT
        kme.id,
        DATE_FORMAT(kme.create_time, '%Y-%m-%d') as create_time,
        kme.merchant_name,
        kma.machine_ratio,
        kma.down_fee_flag,
        IFNULL(SUM(kskm.trade_amt), 0) as this_month_trade
        FROM
        kxs_merchant kme
        LEFT JOIN
        kxs_machine kma ON kme.machine_id = kma.id
        LEFT JOIN
        kxs_stat.${tableName} kskm ON kskm.merchant_id = kme.id
        WHERE
        kme.del_flag = 0
        AND kme.brand_id = #{param.brandId}
        AND kme.user_id = #{param.userId}
        <if test="param.keyword != null and param.keyword != ''">
            AND (
            kme.merchant_no LIKE CONCAT('%', #{param.keyword}, '%')
            OR kme.merchant_phone LIKE CONCAT('%', #{param.keyword}, '%')
            OR kme.merchant_name LIKE CONCAT('%', #{param.keyword}, '%')
            OR kma.pos_sn LIKE CONCAT('%', #{param.keyword}, '%')
            )
        </if>
        <if test="param.actTime != null and param.actTime != ''">
            AND YEAR(kma.act_time) = #{param.actTime}
        </if>
        <if test="param.actStatus != null and param.actStatus != ''">
            AND kma.is_act = #{param.actStatus}
        </if>
        GROUP BY kme.id
        <if test="param.amountSection != null and param.amountSection.length == 2">
            HAVING this_month_trade BETWEEN #{param.amountSection[0]} AND #{param.amountSection[1]}
        </if>
        <if test="param.sort != null and param.sort == 'amount'">
            ORDER BY this_month_trade DESC
        </if>
        <if test="param.sort != null and param.sort == 'time'">
            ORDER BY create_time DESC
        </if>
    </select>
    <select id="merchantTotal" resultType="com.kxs.product.api.vo.kxsapp.merchant.MerchantTotalVO">
        SELECT
            (
                SELECT
                    COUNT(id)
                FROM
                    kxs_merchant
                WHERE
                    del_flag != 1
                AND user_id = #{userId}
            ) AS totalCount,
            (
                SELECT
                    COUNT(is_act)
                FROM
                    kxs_machine
                WHERE
                    del_flag != 1
                  AND user_id = #{userId}
                  AND brand_id = #{brandId}
                  AND DATE(act_time) = CURDATE()
                  AND is_act = 1
            ) AS actCount,
            COUNT(kme.id) AS brandCount,
            SUM(
                    CASE WHEN kma.is_act = 1 THEN 1 ELSE 0 END
            ) AS brandActCount,
            SUM(
                    CASE WHEN kma.is_act = 0 THEN 1 ELSE 0 END
            ) AS brandUnActCount
        FROM
            kxs_merchant kme
            LEFT JOIN kxs_machine kma ON kma.id = kme.machine_id
        WHERE
            kme.del_flag != 1
          AND kme.brand_id = #{brandId}
          AND kme.user_id = #{userId};
     </select>
</mapper>
