<?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.ruoyi.ss.transactionBill.mapper.TransactionBillMapper">

    <resultMap type="TransactionBillVO" id="SmTransactionBillResult" autoMapping="true">
        <result property="transferIds" column="transfer_ids" typeHandler="com.ruoyi.system.mapper.typehandler.StringListTypeHandler"/>
        <result property="suitGearAmount" column="suit_gear_amount" typeHandler="com.ruoyi.system.mapper.typehandler.DecimalSplitListTypeHandler"/>
        <result property="suitGearTime" column="suit_gear_time" typeHandler="com.ruoyi.system.mapper.typehandler.IntegerSplitListTypeHandler"/>
        <result property="deviceTotalEle" column="device_total_ele" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>

    <sql id="BaseColumns">
        stb.bill_id,
        stb.bill_no,
        stb.user_id,
        stb.type,
        stb.device_id,
        stb.mch_id,
        stb.money,
        stb.arrival_amount,
        stb.service_charge,
        stb.create_time,
        stb.remark,
        stb.status,
        stb.channel_id,
        stb.after_balance,
        stb.pay_time,
        stb.expire_time,
        stb.account_no,
        stb.payed_amount,
        stb.transfer_ids,
        stb.channel_cost,
        stb.device_recharge_status,
        stb.suit_id,
        stb.suit_time,
        stb.suit_time_unit,
        stb.suit_start_time,
        stb.suit_end_time,
        stb.suit_expire_time,
        stb.suit_fee_mode,
        stb.suit_fee_type,
        stb.suit_gear_amount,
        stb.suit_gear_time,
        stb.suit_price,
        stb.suit_start_ele,
        stb.suit_end_ele,
        stb.store_id,
        stb.store_name,
        stb.store_address,
        stb.device_name,
        stb.device_no,
        stb.suit_name,
        stb.device_mac,
        stb.device_mac2,
        stb.refund_amount,
        stb.refund_mch_amount,
        stb.refund_service_amount,
        stb.pay_picture,
        stb.withdraw_type,
        stb.offline_image,
        stb.deposit_pay_id,
        stb.pay_id,
        stb.suit_enable_low_power_close,
        stb.suit_low_power,
        stb.device_product_id,
        stb.device_product_id,
        stb.device_service_mode,
        stb.version,
        stb.close_status,
        stb.close_result,
        stb.agent_id,
        stb.agent_mobile,
        stb.suit_enabled_void,
        stb.suit_void_minute,
        stb.suit_void_result,
        stb.suit_void_msg,
        stb.suit_deposit,
        stb.open_msg,
        stb.user_mobile,
        stb.mch_show_mobile,
        stb.mch_show_mobile_price,
        stb.mch_show_mobile_status,
    </sql>

    <sql id="selectSmTransactionBillVo">
        select
            <include refid="BaseColumns"/>
            if(<include refid="isUsing"/>, true, false) as is_using,
            if(<include refid="isFinished"/>, true, false) as is_finished,
            if(su.is_real, su.real_name, su.user_name) as user_name,
            if(su1.is_real, su1.real_name, su1.user_name) as mch_name,
            su1.phonenumber as mch_mobile,
            sd.device_no as device_no,
            sd.total_electri_quantity as device_total_ele,
            su.phonenumber as user_mobile
        from <include refid="rechargeTables"/>
    </sql>

    <sql id="rechargeTables">
        sm_transaction_bill stb
            left join sm_user su on su.user_id = stb.user_id
            left join sm_user su1 on su1.user_id = stb.mch_id
            left join sm_device sd on sd.device_id = stb.device_id
            left join sm_suit ss on ss.suit_id = stb.suit_id
    </sql>

    <!--查询提现VO-->
    <sql id="selectWithdrawVo">
        select
            <include refid="BaseColumns"/>
            if(su.is_real, su.real_name, su.user_name) as user_name,
            if(su1.is_real, su1.real_name, su1.user_name) as mch_name,
            su1.phonenumber as mch_mobile,
            su.phonenumber as user_mobile,
            sc.name as channel_name
        from sm_transaction_bill stb
            left join sm_user su on su.user_id = stb.user_id
            left join sm_user su1 on su1.user_id = stb.mch_id
            left join ss_channel_withdraw sc on sc.channel_id = stb.channel_id
    </sql>


<!--    (-->
<!--    (-->
<!--    and stb.suit_start_time is not null-->
<!--    and stb.suit_start_time &lt; now()-->
<!--    and (stb.suit_end_time is null or stb.suit_end_time > now())-->
<!--    ) or (-->
<!--    stb.suit_fee_type in ('2', '3')-->
<!--    and stb.suit_start_ele is not null-->
<!--    and stb.suit_start_ele &lt; sd.total_electri_quantity-->
<!--    and (stb.suit_end_ele is null or stb.suit_end_ele > sd.total_electri_quantity)-->
<!--    )-->
<!--    )-->

    <sql id="isUsing">
        (
            !(
                stb.suit_start_time is null or stb.suit_start_time > now()
                or (
                    stb.suit_fee_type in ('2', '3')
                    and (stb.suit_start_ele is null or stb.suit_start_ele > sd.total_electri_quantity)
                )
            ) and !(<include refid="isFinished"/>)
        )
    </sql>


    <sql id="isFinished">
        (
            (
                stb.suit_end_time is not null and stb.suit_end_time &lt;= now()
            ) or (
                stb.suit_fee_type in ('2', '3')
                and (stb.suit_end_ele is not null and stb.suit_end_ele &lt;= sd.total_electri_quantity)
            )
        )
    </sql>

    <sql id="searchCondition">
        <if test="query.userId != null "> and stb.user_id = #{query.userId}</if>
        <if test="query.incomeUserId != null "> and (stb.mch_id = #{query.incomeUserId} or stb.agent_id = #{query.incomeUserId}) </if>
        <if test="query.billNo != null and query.billNo != ''"> and stb.bill_no like concat('%', #{query.billNo}, '%')</if>
        <if test="query.billId != null "> and stb.bill_id = #{query.billId}</if>
        <if test="query.deviceId != null "> and stb.device_id = #{query.deviceId}</if>
        <if test="query.type != null "> and stb.type = #{query.type} </if>
        <if test="query.mchId != null "> and stb.mch_id = #{query.mchId}</if>
        <if test="query.userName != null "> and if(su.is_real, su.real_name, su.user_name) like concat('%', #{query.userName}, '%')</if>
        <if test="query.mchName != null "> and if(su1.is_real, su1.real_name, su1.user_name) like concat('%', #{query.mchName}, '%')</if>
        <if test="query.deviceName != null "> and stb.device_name like concat('%', #{query.deviceName}, '%')</if>
        <if test="query.deviceMac != null and query.deviceMac != ''"> and stb.device_mac = #{query.deviceMac}</if>
        <if test="query.deviceMac2 != null  and query.deviceMac2 != ''"> and stb.device_mac2 = #{query.deviceMac2}</if>
        <if test="query.createTime != null"> and stb.create_time = #{query.createTime}</if>
        <if test="query.createDate != null"> and date(stb.create_time) = date(#{query.createDate})</if>
        <if test="query.year != null "> and year(stb.create_time) = #{query.year}</if>
        <if test="query.month != null"> and month(stb.create_time) = #{query.month}</if>
        <if test="query.hour != null"> and hour(stb.create_time) = #{query.hour}</if>
        <if test="query.status != null"> and stb.status = #{query.status}</if>
        <if test="query.expire != null"> and NOW() >= stb.expire_time </if>
        <if test="query.startDate != null"> and date(stb.create_time) >= date(#{query.startDate}) </if>
        <if test="query.endDate != null"> and date(stb.create_time) &lt;= date(#{query.endDate}) </if>
        <if test="query.deviceRechargeStatus != null"> and stb.device_recharge_status = #{query.deviceRechargeStatus} </if>
        <if test="query.suitId != null"> and stb.suit_id = #{query.suitId} </if>
        <if test="query.storeId != null"> and stb.store_id = #{query.storeId} </if>
        <if test="query.endSuitEndTime != null"> and stb.suit_end_time &lt;= #{query.endSuitEndTime}</if>
        <if test="query.startSuitEndTime != null"> and stb.suit_end_time >= #{query.startSuitEndTime}</if>
        <if test="query.startSuitEndEle != null"> and stb.suit_end_ele >= #{query.startSuitEndEle}</if>
        <if test="query.endSuitEndEle != null"> and stb.suit_end_ele &lt;= #{query.endSuitEndEle}</if>
        <if test="query.channelId != null"> and stb.channel_id = #{query.channelId}</if>
        <if test="query.withdrawType != null"> and stb.withdraw_type = #{query.withdrawType}</if>
        <if test="query.money != null"> and stb.money = #{query.money}</if>
        <if test="query.deviceNo != null and query.deviceNo != ''"> and sd.device_no like concat('%', #{query.deviceNo}, '%')</if>
        <if test="query.userMobile != null and query.userMobile != ''"> and stb.user_mobile like concat('%', #{query.userMobile}, '%')</if>
        <if test="query.suitFeeMode != null  and query.suitFeeMode != ''"> and stb.suit_fee_mode = #{query.suitFeeMode}</if>
        <if test="query.suitFeeType != null  and query.suitFeeType != ''"> and stb.suit_fee_type = #{query.suitFeeType}</if>
        <if test="query.suitEnableLowPowerClose != null "> and stb.suit_enable_low_power_close = #{query.suitEnableLowPowerClose}</if>
        <if test="query.createTimeEnd != null "> and stb.create_time &lt;= #{query.createTimeEnd}</if>
        <if test="query.createTimeStart != null "> and stb.create_time >= #{query.createTimeStart}</if>
        <if test="query.deviceProductId != null  and query.deviceProductId != ''"> and stb.device_product_id = #{query.deviceProductId}</if>
        <if test="query.deviceServiceMode != null  and query.deviceServiceMode != ''"> and stb.device_service_mode = #{query.deviceServiceMode}</if>
        <if test="query.version != null "> and stb.version = #{query.version}</if>
        <if test="query.closeStatus != null  and query.closeStatus != ''"> and stb.close_status = #{query.closeStatus}</if>
        <if test="query.agentId != null "> and stb.agent_id = #{query.agentId}</if>
        <if test="query.agentMobile != null  and query.agentMobile != ''"> and stb.agent_mobile like concat('%', #{query.agentMobile}, '%')</if>
        <if test="query.suitEnabledVoid != null "> and stb.suit_enabled_void = #{query.suitEnabledVoid}</if>
        <if test="query.suitVoidResult != null  and query.suitVoidResult != ''"> and stb.suit_void_result = #{query.suitVoidResult}</if>
        <if test="query.openMsg != null  and query.openMsg != ''"> and stb.open_msg like concat('%', #{query.openMsg}, '%')</if>
        <if test="query.mchShowMobileStatus != null  and query.mchShowMobileStatus != ''"> and stb.mch_show_mobile_status = #{query.mchShowMobileStatus}</if>
        <if test="query.isUsing != null">
            <if test="query.isUsing">
                and <include refid="isUsing"/>
            </if>
            <if test="!query.isUsing">
                and !<include refid="isUsing"/>
            </if>
        </if>
        <if test="query.isFinished != null">
            <if test="query.isFinished">
                and <include refid="isFinished"/>
            </if>
            <if test="!query.isFinished">
                and !<include refid="isFinished"/>
            </if>
        </if>
        <if test="query.keyword != null and query.keyword != ''">
            and (
                sd.device_no like concat('%', #{query.keyword}, '%')
                or sd.device_name like concat('%', #{query.keyword}, '%')
                or su.user_name like concat('%', #{query.keyword}, '%')
                or su.phonenumber like concat('%', #{query.keyword}, '%')
            )
        </if>
        <if test="query.suitFeeTypes != null and query.suitFeeTypes.size() > 0">
            and stb.suit_fee_type in
            <foreach item="item" collection="query.suitFeeTypes" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.suitFeeModes != null and query.suitFeeModes.size() > 0">
            and stb.suit_fee_mode in
            <foreach item="item" collection="query.suitFeeModes" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.billIds != null and query.billIds.size() > 0">
            and stb.bill_id in
            <foreach item="item" collection="query.billIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.deviceRechargeStatusList != null and query.deviceRechargeStatusList.size() > 0">
            and stb.device_recharge_status in
            <foreach item="item" collection="query.deviceRechargeStatusList" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.statusList != null and query.statusList.size() > 0">
            and stb.status in
            <foreach item="item" collection="query.statusList" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.storeIds != null and query.storeIds.size() > 0">
            and stb.store_id in
            <foreach item="item" collection="query.storeIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.deviceIds != null and query.deviceIds.size() > 0">
            and stb.device_id in
            <foreach item="item" collection="query.deviceIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.channelIds != null and query.channelIds.size() > 0">
            and stb.channel_id in
            <foreach item="item" collection="query.channelIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.createDateRange != null and query.createDateRange.size() > 1">
            and date(stb.create_time) >= date(#{query.createDateRange[0]}) and date(stb.create_time) &lt;= date(#{query.createDateRange[1]})
        </if>
        <if test="query.payDateRange != null and query.payDateRange.size() > 1">
            and date(stb.pay_time) >= date(#{query.payDateRange[0]}) and date(stb.pay_time) &lt;= date(#{query.payDateRange[1]})
        </if>
        <if test="query.inStaff != null">
            and stb.store_id
            <if test="!query.inStaff">not</if>
            in (<include refid="selectStaffViewStoreIds"/>)
        </if>
    </sql>

    <!--查询员工可查看的店铺ID列表-->
    <sql id="selectStaffViewStoreIds">
        select distinct sss.store_id
        from ss_store_staff sss
        <where>
            <if test="query.staffUserId != null">and sss.user_id = #{query.staffUserId}</if>
            <if test="query.staffPermission != null and query.staffPermission != ''">
                and find_in_set(#{query.staffPermission}, sss.permissions)
            </if>
        </where>
    </sql>

    <select id="selectSmTransactionBillList" parameterType="TransactionBillQuery" resultMap="SmTransactionBillResult">
        <include refid="selectSmTransactionBillVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        order by stb.create_time desc
    </select>

    <select id="selectSmTransactionBillByBillId" parameterType="Long" resultMap="SmTransactionBillResult">
        <include refid="selectSmTransactionBillVo"/>
        where stb.bill_id = #{billId}
    </select>

    <resultMap id="BillCountVo" type="com.ruoyi.dashboard.domain.vo.BillCountVo" autoMapping="true">
        <result property="createDate" column="create_date"/>
        <result property="createHour" column="create_hour"/>
        <result property="createYear" column="create_year"/>
        <result property="createMonth" column="create_month"/>
        <result property="createDay" column="create_day"/>
        <result property="createYearMonth" column="create_year_month"/>
        <result property="storeId" column="store_id"/>
        <result property="deviceId" column="device_id"/>
        <result property="count" column="count" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullIntegerTyperHandler"/>
        <result property="serviceCharge" column="service_charge" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="channelCost" column="channel_cost" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="profit" column="profit" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="rechargeAmount" column="recharge_amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>

    <select id="selectCount" resultMap="BillCountVo">
        SELECT
            <if test="query.groupBy == 'create_date'">
                DATE( stb.create_time ) AS `create_date`,
            </if>
            <if test="query.groupBy == 'create_hour'">
                HOUR( stb.create_time ) AS `create_hour`,
            </if>
            <if test="query.groupBy == 'create_year' or query.groupBy == 'create_year_month' or query.groupBy == 'create_date' or query.groupBy == 'create_month'">
                YEAR( stb.create_time ) AS `create_year`,
            </if>
            <if test="query.groupBy == 'create_month' or query.groupBy == 'create_year_month' or query.groupBy == 'create_date'">
                MONTH( stb.create_time ) AS `create_month`,
            </if>
            <if test="query.groupBy == 'create_day' or query.groupBy == 'create_date'">
                DAY( stb.create_time ) AS `create_day`,
            </if>
            <if test="query.groupBy == 'create_year_month' or query.groupBy == 'create_date' or query.groupBy == 'create_month'">
                DATE_FORMAT( stb.create_time, '%Y-%m' ) AS `create_year_month`,
            </if>
            <if test="query.groupBy == 'store_id'">
                stb.store_id as store_id,
            </if>
            <if test="query.groupBy == 'device_id'">
                stb.device_id as device_id,
            </if>
            SUM(IF( stb.type = '1' and stb.status = '2', stb.arrival_amount, 0 )) AS recharge,
            SUM(IF( stb.type = '1' and stb.status = '2', stb.money, 0 )) AS recharge_amount,
            SUM(IF( stb.type = '2' and stb.status = '14', stb.arrival_amount, 0 )) AS withdraw,
            SUM(stb.service_charge) AS service_charge,
            SUM(stb.channel_cost) AS channel_cost,
            SUM(stb.service_charge - stb.channel_cost) AS profit,
            count(stb.bill_id) AS `count`
        FROM
            sm_transaction_bill stb
        <where>
            <include refid="searchCondition"/>
        </where>
        <if test="query.groupBy != null">
            GROUP BY ${query.groupBy}
        </if>
    </select>

    <select id="selectSmTransactionBillByBillNo" resultMap="SmTransactionBillResult">
        <include refid="selectSmTransactionBillVo"/>
        where stb.bill_no = #{billNo}
        limit 1
    </select>

    <select id="selectWithdrawList" resultMap="SmTransactionBillResult">
        <include refid="selectWithdrawVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        order by stb.create_time desc
    </select>

    <select id="selectWithdrawById" resultMap="SmTransactionBillResult">
        <include refid="selectWithdrawVo"/>
        where stb.bill_id = #{billId}
    </select>

    <select id="selectSimpleCount" resultType="java.lang.Integer">
        select count(stb.bill_id)
        from <include refid="rechargeTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectSumOfMoney" resultType="java.math.BigDecimal">
        select sum(stb.money)
        from <include refid="rechargeTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <resultMap id="TransactionAmountVO" type="TransactionAmountVO">
        <result property="money" column="money" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="serviceAmount" column="service_amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="arrivalAmount" column="arrival_amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="channelCost" column="channel_cost" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>

    <select id="selectCommonSumOfMoney" resultMap="TransactionAmountVO">
        select
            <if test="groupBy != null and groupBy != ''">
                <if test="groupBy == 'create_date'">
                    date(stb.create_time) as `key`,
                </if>
            </if>
            sum(stb.money) as money,
            sum(stb.service_charge) as service_amount,
            sum(stb.arrival_amount) as arrival_amount,
            sum(stb.channel_cost) as channel_cost
        from <include refid="rechargeTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        <if test="groupBy != null and groupBy != ''">
            group by `key`
        </if>
    </select>

    <select id="selectCountGroupBy" resultType="com.alibaba.fastjson2.JSONObject">
        select
            <if test="groupBy != null and groupBy != ''">
                ${groupBy} as `key`,
            </if>
            count(stb.bill_id) as `count`
        from <include refid="rechargeTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        <if test="groupBy != null and groupBy != ''">
            group by `key`
        </if>
    </select>

    <insert id="insertSmTransactionBill" parameterType="TransactionBill" useGeneratedKeys="true" keyProperty="billId">
        <selectKey resultType="Long" order="AFTER" keyProperty="billId">
            SELECT LAST_INSERT_ID();
        </selectKey>
        insert into sm_transaction_bill
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="billNo != null">bill_no,</if>
            <if test="userId != null">user_id,</if>
            <if test="type != null">type,</if>
            <if test="deviceId != null">device_id,</if>
            <if test="mchId != null">mch_id,</if>
            <if test="money != null">money,</if>
            <if test="arrivalAmount != null">arrival_amount,</if>
            <if test="serviceCharge != null">service_charge,</if>
            <if test="createTime != null">create_time,</if>
            <if test="remark != null">remark,</if>
            <if test="status != null ">`status`,</if>
            <if test="channelId != null ">channel_id,</if>
            <if test="afterBalance != null ">after_balance,</if>
            <if test="payTime != null ">pay_time,</if>
            <if test="expireTime != null ">expire_time,</if>
            <if test="accountNo != null ">account_no,</if>
            <if test="payedAmount != null ">payed_amount,</if>
            <if test="transferIds != null ">transfer_ids,</if>
            <if test="channelCost != null ">channel_cost,</if>
            <if test="deviceRechargeStatus != null ">device_recharge_status,</if>
            <if test="suitId != null ">suit_id,</if>
            <if test="suitTime != null ">suit_time,</if>
            <if test="suitTimeUnit != null ">suit_time_unit,</if>
            <if test="suitStartTime != null ">suit_start_time,</if>
            <if test="suitEndTime != null ">suit_end_time,</if>
            <if test="suitExpireTime != null ">suit_expire_time,</if>
            <if test="suitFeeMode != null">suit_fee_mode,</if>
            <if test="suitFeeType != null">suit_fee_type,</if>
            <if test="suitGearAmount != null">suit_gear_amount,</if>
            <if test="suitGearTime != null">suit_gear_time,</if>
            <if test="suitPrice != null">suit_price,</if>
            <if test="suitStartEle != null">suit_start_ele,</if>
            <if test="suitEndEle != null">suit_end_ele,</if>
            <if test="storeId != null ">store_id,</if>
            <if test="storeName != null ">store_name,</if>
            <if test="storeAddress != null ">store_address,</if>
            <if test="deviceName != null ">device_name,</if>
            <if test="deviceNo != null ">device_no,</if>
            <if test="suitName != null ">suit_name,</if>
            <if test="deviceMac != null ">device_mac,</if>
            <if test="deviceMac2 != null">device_mac2,</if>
            <if test="deviceServiceMode != null">device_service_mode,</if>
            <if test="refundAmount != null">refund_amount,</if>
            <if test="refundMchAmount != null">refund_mch_amount,</if>
            <if test="refundServiceAmount != null">refund_service_amount,</if>
            <if test="payPicture != null">pay_picture,</if>
            <if test="withdrawType != null">withdraw_type,</if>
            <if test="offlineImage != null">offline_image,</if>
            <if test="depositPayId != null">deposit_pay_id,</if>
            <if test="payId != null">pay_id,</if>
            <if test="suitEnableLowPowerClose != null">suit_enable_low_power_close,</if>
            <if test="suitLowPower != null">suit_low_power,</if>
            <if test="deviceProductId != null">device_product_id,</if>
            <if test="version != null">version,</if>
            <if test="closeStatus != null">close_status,</if>
            <if test="closeResult != null">close_result,</if>
            <if test="agentId != null">agent_id,</if>
            <if test="agentMobile != null">agent_mobile,</if>
            <if test="suitEnabledVoid != null">suit_enabled_void,</if>
            <if test="suitVoidMinute != null">suit_void_minute,</if>
            <if test="suitVoidResult != null">suit_void_result,</if>
            <if test="suitVoidMsg != null">suit_void_msg,</if>
            <if test="suitDeposit != null">suit_deposit,</if>
            <if test="openMsg != null">open_msg,</if>
            <if test="userMobile != null">user_mobile,</if>
            <if test="mchShowMobile != null">mch_show_mobile,</if>
            <if test="mchShowMobilePrice != null">mch_show_mobile_price,</if>
            <if test="mchShowMobileStatus != null and mchShowMobileStatus != ''">mch_show_mobile_status,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="billNo != null">#{billNo},</if>
            <if test="userId != null">#{userId},</if>
            <if test="type != null">#{type},</if>
            <if test="deviceId != null">#{deviceId},</if>
            <if test="mchId != null">#{mchId},</if>
            <if test="money != null">#{money},</if>
            <if test="arrivalAmount != null">#{arrivalAmount},</if>
            <if test="serviceCharge != null">#{serviceCharge},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="status != null">#{status},</if>
            <if test="channelId != null">#{channelId},</if>
            <if test="afterBalance != null">#{afterBalance},</if>
            <if test="payTime != null">#{payTime},</if>
            <if test="expireTime != null">#{expireTime},</if>
            <if test="accountNo != null">#{accountNo},</if>
            <if test="payedAmount != null">#{payedAmount},</if>
            <if test="transferIds != null">#{transferIds,typeHandler=com.ruoyi.system.mapper.typehandler.StringListTypeHandler},</if>
            <if test="channelCost != null">#{channelCost},</if>
            <if test="deviceRechargeStatus != null">#{deviceRechargeStatus},</if>
            <if test="suitId != null">#{suitId},</if>
            <if test="suitTime != null">#{suitTime},</if>
            <if test="suitTimeUnit != null">#{suitTimeUnit},</if>
            <if test="suitStartTime != null">#{suitStartTime},</if>
            <if test="suitEndTime != null">#{suitEndTime},</if>
            <if test="suitExpireTime != null">#{suitExpireTime},</if>
            <if test="suitFeeMode != null">#{suitFeeMode},</if>
            <if test="suitFeeType != null">#{suitFeeType},</if>
            <if test="suitGearAmount != null">#{suitGearAmount,typeHandler=com.ruoyi.system.mapper.typehandler.DecimalSplitListTypeHandler},</if>
            <if test="suitGearTime != null">#{suitGearTime,typeHandler=com.ruoyi.system.mapper.typehandler.IntegerSplitListTypeHandler},</if>
            <if test="suitPrice != null">#{suitPrice},</if>
            <if test="suitStartEle != null">#{suitStartEle},</if>
            <if test="suitEndEle != null">#{suitEndEle},</if>
            <if test="storeId != null ">#{storeId},</if>
            <if test="storeName != null ">#{storeName},</if>
            <if test="storeAddress != null ">#{storeAddress},</if>
            <if test="deviceName != null ">#{deviceName},</if>
            <if test="deviceNo != null ">#{deviceNo},</if>
            <if test="suitName != null ">#{suitName},</if>
            <if test="deviceMac != null ">#{deviceMac},</if>
            <if test="deviceMac2 != null">#{deviceMac2},</if>
            <if test="deviceServiceMode != null">#{deviceServiceMode},</if>
            <if test="refundAmount != null">#{refundAmount},</if>
            <if test="refundMchAmount != null">#{refundMchAmount},</if>
            <if test="refundServiceAmount != null">#{refundServiceAmount},</if>
            <if test="payPicture != null">#{payPicture},</if>
            <if test="withdrawType != null">#{withdrawType},</if>
            <if test="offlineImage != null">#{offlineImage},</if>
            <if test="depositPayId != null">#{depositPayId},</if>
            <if test="payId != null">#{payId},</if>
            <if test="suitEnableLowPowerClose != null">#{suitEnableLowPowerClose},</if>
            <if test="suitLowPower != null">#{suitLowPower},</if>
            <if test="deviceProductId != null">#{deviceProductId},</if>
            <if test="version != null">#{version},</if>
            <if test="closeStatus != null">#{closeStatus},</if>
            <if test="closeResult != null">#{closeResult},</if>
            <if test="agentId != null">#{agentId},</if>
            <if test="agentMobile != null">#{agentMobile},</if>
            <if test="suitEnabledVoid != null">#{suitEnabledVoid},</if>
            <if test="suitVoidMinute != null">#{suitVoidMinute},</if>
            <if test="suitVoidResult != null">#{suitVoidResult},</if>
            <if test="suitVoidMsg != null">#{suitVoidMsg},</if>
            <if test="suitDeposit != null">#{suitDeposit},</if>
            <if test="openMsg != null">#{openMsg},</if>
            <if test="userMobile != null">#{userMobile},</if>
            <if test="mchShowMobile != null">#{mchShowMobile},</if>
            <if test="mchShowMobilePrice != null">#{mchShowMobilePrice},</if>
            <if test="mchShowMobileStatus != null and mchShowMobileStatus != ''">#{mchShowMobileStatus},</if>
         </trim>
    </insert>


    <update id="addRefundAmount">
        update sm_transaction_bill
        set refund_amount = refund_amount + #{refundAmount},
            refund_mch_amount = refund_mch_amount + #{refundMchAmount},
            refund_service_amount = refund_service_amount + #{refundServiceAmount}
        where bill_id = #{billId}
    </update>

    <update id="updateSmTransactionBill" parameterType="TransactionBill">
        update sm_transaction_bill stb
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where stb.bill_id = #{data.billId}
    </update>

    <sql id="updateColumns">
        <if test="data.userId != null">stb.user_id = #{data.userId},</if>
        <if test="data.type != null">stb.`type` = #{data.type},</if>
        <if test="data.deviceId != null">stb.device_id = #{data.deviceId},</if>
        <if test="data.mchId != null">stb.mch_id = #{data.mchId},</if>
        <if test="data.money != null">stb.money = #{data.money},</if>
        <if test="data.arrivalAmount != null">stb.arrival_amount = #{data.arrivalAmount},</if>
        <if test="data.serviceCharge != null">stb.service_charge = #{data.serviceCharge},</if>
        <if test="data.createTime != null">stb.create_time = #{data.createTime},</if>
        <if test="data.remark != null">stb.remark = #{data.remark},</if>
        <if test="data.status != null">stb.`status` = #{data.status},</if>
        <if test="data.channelId != null">stb.channel_id = #{data.channelId},</if>
        <if test="data.afterBalance != null">stb.after_balance = #{data.afterBalance},</if>
        <if test="data.payTime != null">stb.pay_time = #{data.payTime},</if>
        <if test="data.expireTime != null">stb.expire_time = #{data.expireTime},</if>
        <if test="data.accountNo != null">stb.account_no = #{data.accountNo},</if>
        <if test="data.payedAmount != null">stb.payed_amount = #{data.payedAmount},</if>
        <if test="data.transferIds != null">stb.transfer_ids = #{data.transferIds,typeHandler=com.ruoyi.system.mapper.typehandler.StringListTypeHandler},</if>
        <if test="data.channelCost != null">stb.channel_cost = #{data.channelCost},</if>
        <if test="data.suitId != null">stb.suit_id = #{data.suitId},</if>
        <if test="data.suitTime != null">stb.suit_time = #{data.suitTime},</if>
        <if test="data.suitTimeUnit != null">stb.suit_time_unit = #{data.suitTimeUnit},</if>
        <if test="data.suitStartTime != null">stb.suit_start_time = #{data.suitStartTime},</if>
        <if test="data.suitEndTime != null">stb.suit_end_time = #{data.suitEndTime},</if>
        <if test="data.suitExpireTime != null">stb.suit_expire_time = #{data.suitExpireTime},</if>
        <if test="data.suitFeeMode != null">stb.suit_fee_mode = #{data.suitFeeMode},</if>
        <if test="data.suitFeeType != null">stb.suit_fee_type = #{data.suitFeeType},</if>
        <if test="data.suitGearAmount != null">stb.suit_gear_amount = #{data.suitGearAmount,typeHandler=com.ruoyi.system.mapper.typehandler.DecimalSplitListTypeHandler},</if>
        <if test="data.suitGearTime != null">stb.suit_gear_time = #{data.suitGearTime,typeHandler=com.ruoyi.system.mapper.typehandler.IntegerSplitListTypeHandler},</if>
        <if test="data.suitPrice != null">stb.suit_price = #{data.suitPrice},</if>
        <if test="data.suitStartEle != null">stb.suit_start_ele = #{data.suitStartEle},</if>
        <if test="data.suitEndEle != null">stb.suit_end_ele = #{data.suitEndEle},</if>
        <if test="data.storeId != null ">stb.store_id = #{data.storeId},</if>
        <if test="data.storeName != null ">stb.store_name = #{data.storeName},</if>
        <if test="data.storeAddress != null ">stb.store_address = #{data.storeAddress},</if>
        <if test="data.deviceName != null ">stb.device_name = #{data.deviceName},</if>
        <if test="data.deviceNo != null ">stb.device_no = #{data.deviceNo},</if>
        <if test="data.suitName != null ">stb.suit_name = #{data.suitName},</if>
        <if test="data.deviceMac != null ">stb.device_mac = #{data.deviceMac},</if>
        <if test="data.deviceMac2 != null">stb.device_mac2 = #{data.deviceMac2},</if>
        <if test="data.refundAmount != null">stb.refund_amount = #{data.refundAmount},</if>
        <if test="data.refundMchAmount != null">stb.refund_mch_amount = #{data.refundMchAmount},</if>
        <if test="data.refundServiceAmount != null">stb.refund_service_amount = #{data.refundServiceAmount},</if>
        <if test="data.payPicture != null">stb.pay_picture = #{data.payPicture},</if>
        <if test="data.withdrawType != null">stb.withdraw_type = #{data.withdrawType},</if>
        <if test="data.offlineImage != null">stb.offline_image = #{data.offlineImage},</if>
        <if test="data.depositPayId != null">stb.deposit_pay_id = #{data.depositPayId},</if>
        <if test="data.payId != null">stb.pay_id = #{data.payId},</if>
        <if test="data.suitEnableLowPowerClose != null">stb.suit_enable_low_power_close = #{data.suitEnableLowPowerClose},</if>
        <if test="data.suitLowPower != null">stb.suit_low_power = #{data.suitLowPower},</if>
        <if test="data.deviceProductId != null">stb.device_product_id = #{data.deviceProductId},</if>
        <if test="data.deviceRechargeStatus != null">stb.device_recharge_status = #{data.deviceRechargeStatus},</if>
        <if test="data.deviceServiceMode != null">stb.device_service_mode = #{data.deviceServiceMode},</if>
        <if test="data.version != null">stb.version = #{data.version},</if>
        <if test="data.closeStatus != null">stb.close_status = #{data.closeStatus},</if>
        <if test="data.closeResult != null">stb.close_result = #{data.closeResult},</if>
        <if test="data.agentId != null">stb.agent_id = #{data.agentId},</if>
        <if test="data.agentMobile != null">stb.agent_mobile = #{data.agentMobile},</if>
        <if test="data.suitEnabledVoid != null">stb.suit_enabled_void = #{data.suitEnabledVoid},</if>
        <if test="data.suitVoidMinute != null">stb.suit_void_minute = #{data.suitVoidMinute},</if>
        <if test="data.suitVoidResult != null">stb.suit_void_result = #{data.suitVoidResult},</if>
        <if test="data.suitVoidMsg != null">stb.suit_void_msg = #{data.suitVoidMsg},</if>
        <if test="data.suitDeposit != null">stb.suit_deposit = #{data.suitDeposit},</if>
        <if test="data.openMsg != null">stb.open_msg = #{data.openMsg},</if>
        <if test="data.userMobile != null">stb.user_mobile = #{data.userMobile},</if>
        <if test="data.mchShowMobile != null">stb.mch_show_mobile = #{data.mchShowMobile},</if>
        <if test="data.mchShowMobilePrice != null">stb.mch_show_mobile_price = #{data.mchShowMobilePrice},</if>
        <if test="data.mchShowMobileStatus != null and data.mchShowMobileStatus != ''">stb.mch_show_mobile_status = #{data.mchShowMobileStatus},</if>
    </sql>

    <update id="updateByQuery">
        update sm_transaction_bill stb
            left join sm_device sd on sd.device_id = stb.device_id
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        <where>
            <include refid="searchCondition"/>
        </where>
    </update>

    <update id="updatechannelId">
        update sm_transaction_bill
        set channel_id = #{channelId},
            account_no = #{accountNo}
        where bill_id = #{billId} and status = '1'
    </update>

    <update id="rechargeSuccess">
        update sm_transaction_bill
        set status = '2',
            pay_time = #{payTime}
        where bill_id = #{billId} and status = '6' and type = '1'
    </update>

    <delete id="deleteSmTransactionBillByBillId" parameterType="Long">
        delete from sm_transaction_bill where bill_id = #{billId}
    </delete>

    <delete id="deleteSmTransactionBillByBillIds" parameterType="String">
        delete from sm_transaction_bill where bill_id in
        <foreach item="billId" collection="array" open="(" separator="," close=")">
            #{billId}
        </foreach>
    </delete>

    <update id="cancelRecharge">
        update sm_transaction_bill
        set status = #{status}
        where bill_no = #{billNo}
          and status in ('1', '8')
          and `type` = '1'
          and !(status = '1' and suit_fee_type in ('3', '4'))
    </update>

    <sql id="approvalWithDraw">
        and status = '11' and `type` = '2'
    </sql>

    <update id="doPassWithdraw">
        update sm_transaction_bill
        set status = '12',
            remark = #{remark}
        where bill_id = #{billId}
            <include refid="approvalWithDraw"/>
    </update>

    <update id="doRejectWithdraw">
        update sm_transaction_bill
        set status = '13',
            remark = #{remark}
        where bill_id = #{billId}
            <include refid="approvalWithDraw"/>
    </update>

    <update id="doPayWithdraw">
        update sm_transaction_bill
        set status = '16'
        where bill_id = #{billId} and status = '12'
    </update>

    <update id="withdrawSuccess">
        update sm_transaction_bill
        set status = '14',
            pay_time = #{payTime}
        where bill_id = #{billId} and status = '16'
    </update>

    <update id="withdrawFailed">
        update sm_transaction_bill
        set status = '15'
        where bill_id = #{billId} and status = '16'
    </update>

    <update id="paying">
        update sm_transaction_bill
        set status = '6'
        where bill_id = #{billId} and status = '1' and `type` = '1'
    </update>

    <update id="updateDeviceRechargeStatus">
        update sm_transaction_bill
        set device_recharge_status = #{status},
            open_msg = #{openMsg}
        where bill_id = #{billId}
          and `type` = '1'
          and device_recharge_status not in('1', '3')
    </update>

    <update id="updateDeviceRechargeStatusByIds">
        update sm_transaction_bill
        set device_recharge_status = #{status}
        where `type` = '1' and device_recharge_status != '1' and bill_id in
        <foreach item="billId" collection="billIds" open="(" separator="," close=")">
            #{billId}
        </foreach>
    </update>

    <update id="bluetoothRechargeSuccess">
        update sm_transaction_bill
        set device_recharge_status = '1'
        where bill_no = #{billNo} and type = '1' and device_recharge_status = '3' and status = '2'
    </update>

    <update id="batchUpdateSuitEndTime">
        update sm_transaction_bill
        set suit_end_time = #{time}
        where bill_id in
        <foreach collection="billIds" separator="," item="item" close=")" open="(">
            #{item}
        </foreach>
    </update>

</mapper>
