<?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.bonus.core.account.v3.mapper.AccExceptionRecordMapper">
    <select id="selectAccExceptionRecordList" resultType="com.bonus.core.account.v3.model.AccExceptionRecord">
        SELECT id,
               cust_id,
               pay_channel,
               amount,
               relation_id,
               wallet_pay_amount,
               sub_pay_amount,
               red_pay_amount,
               serial_num,
               record_status,
               remark
        FROM acc_exception_record
        <where>
            cust_id IS NOT NULL
            <if test="status != null">
                and record_status = #{status}
            </if>
            <if test="startTime != null">
                and crtime <![CDATA[ >= ]]> #{startTime}
            </if>
        </where>
        ORDER BY crtime ASC
    </select>
    <!--    离线三方支付异常记录，失败的记录-->
    <select id="queryAccExceptionRecordPage" resultType="com.bonus.core.account.v3.web.vo.AccExceptionRecordVO">
        SELECT
        t1.id AS recordId,
        t1.cust_id,
        t1.serial_num,
        t1.amount AS realAmount,
        t1.remark AS errorMsg,
        t2.cust_name,
        t2.cust_num,
        t2.mobile,
        t2.org_full_name,
        t4.order_id,
        t4.mac_order_id,
        t4.order_time,
        t4.source_type,
        t4.pay_channel,
        t4.pay_type,
        t4.order_state,
        t4.machine_num,
        t4.machine_sn,
        t5.device_name AS machineName,
        t4.nu_clear_mode
        FROM
        acc_exception_record AS t1
        LEFT JOIN cust_info AS t2 ON t1.cust_id = t2.cust_id
        LEFT JOIN trade_order_recharge AS t3 ON t3.trade_id = t1.relation_id
        LEFT JOIN order_info AS t4 ON t4.order_id = t3.order_recharge_id
        LEFT JOIN device_info t5 ON t5.device_sn = t4.machine_sn
        <include refid="query_acc_exception_record_where_sql"></include>
        ORDER BY t1.uptime DESC
    </select>
    <!--    离线三方支付异常记录，失败的记录-合计-->
    <select id="queryAccExceptionRecordPageSum"
            resultType="com.bonus.core.account.v3.web.vo.AccExceptionRecordVO">
        SELECT
        SUM(t1.amount) AS realAmount
        FROM
        acc_exception_record AS t1
        LEFT JOIN cust_info AS t2 ON t1.cust_id = t2.cust_id
        LEFT JOIN trade_order_recharge AS t3 ON t3.trade_id = t1.relation_id
        LEFT JOIN order_info AS t4 ON t4.order_id = t3.order_recharge_id
        LEFT JOIN device_info t5 ON t5.device_sn = t4.machine_sn
        <include refid="query_acc_exception_record_where_sql"></include>
    </select>

    <!--    离线三方支付异常记录，失败的记录 where-->
    <sql id="query_acc_exception_record_where_sql">
        <where>
            t1.record_status = 3
            <if test="param.startDateTime != null">
                and t1.uptime <![CDATA[ >= ]]> #{param.startDateTime}
            </if>
            <if test="param.endDateTime != null">
                and t1.uptime <![CDATA[ <= ]]> #{param.endDateTime}
            </if>
            <if test="param.orderId != null">
                and t4.order_id like CONCAT('%',#{param.orderId},'%')
            </if>
            <if test="param.keyword != null and param.keyword != '' ">
                and (t2.cust_num LIKE CONCAT('%', #{param.keyword},'%')
                    or t2.cust_name = #{param.keyword,typeHandler=net.xnzn.core.common.encrypt.SM4EncDecHandler}
                    or t2.mobile = #{param.keyword,typeHandler=net.xnzn.core.common.encrypt.SM4EncDecHandler}
                    or t2.mobile_suffix = #{param.keyword,typeHandler=net.xnzn.core.common.encrypt.SM4EncDecHandler}
                    or t2.cust_name_like LIKE CONCAT('%',#{param.keyword,typeHandler=net.xnzn.core.common.encrypt.CipherQueryLikeHandler},'%')
                )
            </if>
            <if test="param.macOrderId != null">
                and t4.mac_order_id like CONCAT('%',#{param.macOrderId},'%')
            </if>
            <if test="param.macKeyWord != null and param.macKeyWord != '' ">
                and concat(t5.device_sn, t5.device_num, IFNULL(t5.device_name, '')) like CONCAT('%',#{param.macKeyWord},'%')
            </if>
            <if test="param.serialNum != null">
                and t1.serial_num like CONCAT('%',#{param.serialNum},'%')
            </if>
        </where>
    </sql>
</mapper>
