<?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.receiveBill.mapper.ReceiveBillMapper">

    <resultMap type="ReceiveBillVO" id="ReceiveBillResult" autoMapping="true"/>

    <sql id="selectReceiveBillVo">
        select
            srb.bill_id,
            srb.user_id,
            srb.device_id,
            srb.type,
            srb.status,
            srb.bill_time,
            srb.amount,
            srb.description,
            srb.create_time,
            srb.received_amount,
            if(su.is_real, su.real_name, su.user_name) as user_name,
            sd.device_name as device_name,
            sd.device_no as device_no
        from <include refid="selectTables"/>
    </sql>

    <sql id="selectTables">
        ss_receive_bill srb
        left join sm_user su on su.user_id = srb.user_id
        left join sm_device sd on sd.device_id = srb.device_id
    </sql>

    <sql id="searchCondition">
        <if test="query.billId != null "> and srb.bill_id = #{query.billId}</if>
        <if test="query.userId != null "> and srb.user_id = #{query.userId}</if>
        <if test="query.deviceId != null "> and srb.device_id = #{query.deviceId}</if>
        <if test="query.billYear != null "> and year(srb.bill_time) = #{query.billYear}</if>
        <if test="query.billMonth != null "> and month(srb.bill_time) = #{query.billMonth}</if>
        <if test="query.billDate != null "> and date(srb.bill_time) = #{query.billDate}</if>
        <if test="query.type != null  and query.type != ''"> and srb.type = #{query.type}</if>
        <if test="query.status != null  and query.status != ''"> and srb.status = #{query.status}</if>
        <if test="query.userName != null  and query.userName != ''"> and if(su.is_real, su.real_name, su.user_name) like concat('%', #{query.userName}, '%')</if>
        <if test="query.deviceName != null  and query.deviceName != ''"> and sd.device_name like concat('%', #{query.deviceName}, '%')</if>
        <if test="query.deviceNo != null  and query.deviceNo != ''"> and sd.device_no like concat('%', #{query.deviceNo}, '%')</if>
        <if test="query.description != null  and query.description != ''"> and srb.description like concat('%', #{query.description}, '%')</if>
        <if test="query.startDate != null">
            and date(srb.create_time) >= #{query.startDate}
        </if>
        <if test="query.endDate != null">
            and date(srb.create_time) &lt;= #{query.endDate}
        </if>
    </sql>

    <select id="selectReceiveBillList" parameterType="ReceiveBillQuery" resultMap="ReceiveBillResult">
        <include refid="selectReceiveBillVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectReceiveBillByBillId" parameterType="Long" resultMap="ReceiveBillResult">
        <include refid="selectReceiveBillVo"/>
        where srb.bill_id = #{billId}
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(srb.bill_id)
        from <include refid="selectTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectSumOfAmount" resultType="java.math.BigDecimal">
        select sum(srb.amount)
        from <include refid="selectTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <resultMap id="ReceiveAmountVO" type="ReceiveAmountVO">
        <result property="amount" column="amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="receivedAmount" column="received_amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>

    <select id="selectCommonSumOfAmount" resultMap="ReceiveAmountVO">
        select
            <if test="groupBy != null and groupBy != ''">
                <if test="groupBy == 'create_date'">
                    date(srb.create_time) as `key`,
                </if>
            </if>
            sum(srb.amount) as `amount`,
            sum(srb.received_amount) as received_amount
        from <include refid="selectTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        <if test="groupBy != null and groupBy != ''">
            group by `key`
        </if>
    </select>

    <insert id="insertReceiveBill" parameterType="ReceiveBill" useGeneratedKeys="true" keyProperty="billId">
        <selectKey keyProperty="billId" resultType="Long" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into ss_receive_bill
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="deviceId != null">device_id,</if>
            <if test="type != null and type != ''">type,</if>
            <if test="status != null and status != ''">`status`,</if>
            <if test="billTime != null">bill_time,</if>
            <if test="amount != null">amount,</if>
            <if test="description != null and description != ''">`description`,</if>
            <if test="createTime != null">create_time,</if>
            <if test="receivedAmount != null">received_amount,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="deviceId != null">#{deviceId},</if>
            <if test="type != null and type != ''">#{type},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="billTime != null">#{billTime},</if>
            <if test="amount != null">#{amount},</if>
            <if test="description != null and description != ''">#{description},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="receivedAmount != null">#{receivedAmount},</if>
         </trim>
    </insert>

    <update id="updateReceiveBill" parameterType="ReceiveBill">
        update ss_receive_bill
        <trim prefix="SET" suffixOverrides=",">
            <if test="data.userId != null">user_id = #{data.userId},</if>
            <if test="data.deviceId != null">device_id = #{data.deviceId},</if>
            <if test="data.type != null and data.type != ''">type = #{data.type},</if>
            <if test="data.status != null and data.status != ''">`status` = #{data.status},</if>
            <if test="data.billTime != null">bill_time = #{data.billTime},</if>
            <if test="data.amount != null">amount = #{data.amount},</if>
            <if test="data.description != null and data.description != ''">`description` = #{data.description},</if>
            <if test="data.createTime != null">create_time = #{data.createTime},</if>
            <if test="data.receivedAmount != null">received_amount = #{data.receivedAmount},</if>
        </trim>
        where bill_id = #{billId}
    </update>

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

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