<?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.meterReadingRecord.mapper.SmMeterReadingRecordMapper">

    <resultMap type="SmMeterReadingRecordVo" id="SmMeterReadingRecordResult">
        <result property="recordId"    column="record_id"    />
        <result property="deviceId"    column="device_id"    />
        <result property="userId"    column="user_id"    />
        <result property="totalElectriQuantity"    column="total_electri_quantity"  typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"   />
        <result property="createTime"    column="create_time"    />
        <result property="createDate"    column="create_date"    />
        <result property="createHour"    column="create_hour"    />
        <result property="createMonth"    column="create_month"    />
        <result property="createDay"    column="create_day"    />
        <result property="usedElectriQuantity"    column="used_electri_quantity"  typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"   />
        <result property="groupUsedElectriQuantity"    column="group_used_electri_quantity"  typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"  />
    </resultMap>

    <sql id="selectSmMeterReadingRecordVo">
        select
            smrr.record_id,
            smrr.device_id,
            smrr.user_id,
            smrr.total_electri_quantity,
            smrr.create_time,
            smrr.used_electri_quantity
        from sm_meter_reading_record smrr
    </sql>

    <sql id="searchCondition">
        <if test="deviceId != null "> and smrr.device_id = #{deviceId}</if>
        <if test="userId != null "> and smrr.user_id = #{userId}</if>
        <if test="createDate != null"> and date(create_time) = date(#{createDate})</if>
        <if test="year != null "> and year(smrr.create_time) = #{year}</if>
        <if test="month != null"> and month(smrr.create_time) = #{month}</if>
    </sql>

    <select id="selectSmMeterReadingRecordList" parameterType="SmMeterReadingRecordQuery" resultMap="SmMeterReadingRecordResult">
        <include refid="selectSmMeterReadingRecordVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        ORDER BY smrr.create_time DESC
    </select>

    <select id="selectSmMeterReadingRecordByRecordId" parameterType="Long" resultMap="SmMeterReadingRecordResult">
        <include refid="selectSmMeterReadingRecordVo"/>
        where record_id = #{recordId}
    </select>

    <sql id="selectGroupBy">
        <if test="groupBy == 'create_month' or groupBy == 'create_date' or groupBy == 'create_hour'">
            month( smrr.create_time ) create_month,
        </if>
        <if test="groupBy == 'create_date' or groupBy == 'create_hour'">
            date( smrr.create_time ) create_date,
            day(smrr.create_time) create_day,
        </if>
        <if test="groupBy == 'create_hour'">
            hour( smrr.create_time ) create_hour,
        </if>
        sum(smrr.used_electri_quantity) group_used_electri_quantity
    </sql>

    <select id="selectLatest" resultMap="SmMeterReadingRecordResult">
        <trim prefix="select" suffixOverrides=",">
            smrr.*,
            <include refid="selectGroupBy"/>
        </trim>
        FROM sm_meter_reading_record smrr
        <where>
            smrr.record_id IN (
                SELECT max( record_id )
                FROM sm_meter_reading_record
                <where>
                    <include refid="searchCondition"/>
                </where>
                <trim prefix="GROUP BY" suffixOverrides=",">
                    device_id,
                    <if test="groupBy == 'create_month'">
                        month( create_time )
                    </if>
                    <if test="groupBy == 'create_date'">
                        date( create_time )
                    </if>
                    <if test="groupBy == 'create_hour'">
                        hour( create_time )
                    </if>
                </trim>
            )
        </where>
        <trim prefix="GROUP BY" suffixOverrides=",">
            smrr.device_id,
            <if test="groupBy != null">
                ${groupBy}
            </if>
        </trim>
    </select>

    <select id="selectCount" resultMap="SmMeterReadingRecordResult">
        <trim prefix="select" suffixOverrides=",">
            smrr.*,
            <include refid="selectGroupBy"/>
        </trim>
        FROM sm_meter_reading_record smrr
        <where>
            <include refid="searchCondition"/>
        </where>
        <trim prefix="GROUP BY" suffixOverrides=",">
            smrr.device_id,
            <if test="groupBy != null">
                ${groupBy}
            </if>
        </trim>
    </select>

    <insert id="insertSmMeterReadingRecord" parameterType="SmMeterReadingRecord">
        insert into sm_meter_reading_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="recordId != null">record_id,</if>
            <if test="deviceId != null">device_id,</if>
            <if test="userId != null">user_id,</if>
            <if test="totalElectriQuantity != null">total_electri_quantity,</if>
            <if test="createTime != null">create_time,</if>
            <if test="usedElectriQuantity != null">used_electri_quantity,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="recordId != null">#{recordId},</if>
            <if test="deviceId != null">#{deviceId},</if>
            <if test="userId != null">#{userId},</if>
            <if test="totalElectriQuantity != null">#{totalElectriQuantity},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="usedElectriQuantity != null">#{usedElectriQuantity},</if>
         </trim>
    </insert>


    <insert id="batchInsert">
        insert into sm_meter_reading_record(device_id, user_id, total_electri_quantity, create_time, used_electri_quantity)
        <foreach collection="list" open="values" separator="," item="i">
            <trim prefix="(" suffixOverrides="," suffix=")">
                #{i.deviceId},
                #{i.userId},
                #{i.totalElectriQuantity},
                #{i.createTime},
                #{i.usedElectriQuantity},
            </trim>
        </foreach>
    </insert>

    <update id="updateSmMeterReadingRecord" parameterType="SmMeterReadingRecord">
        update sm_meter_reading_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceId != null">device_id = #{deviceId},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="totalElectriQuantity != null">total_electri_quantity = #{totalElectriQuantity},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="usedElectriQuantity != null">used_electri_quantity = #{usedElectriQuantity},</if>
        </trim>
        where record_id = #{recordId}
    </update>

    <delete id="deleteSmMeterReadingRecordByRecordId" parameterType="Long">
        delete from sm_meter_reading_record where record_id = #{recordId}
    </delete>

    <delete id="deleteSmMeterReadingRecordByRecordIds" parameterType="String">
        delete from sm_meter_reading_record where record_id in
        <foreach item="recordId" collection="array" open="(" separator="," close=")">
            #{recordId}
        </foreach>
    </delete>
</mapper>
