<?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.pwt.web.mapper.PwtChangeBatteryRecordMapper">
    
    <resultMap type="PwtChangeBatteryRecord" id="PwtChangeBatteryRecordResult">
        <result property="recordId"    column="record_id"    />
        <result property="vehicleNo"    column="vehicle_no"    />
        <result property="vin"    column="vin"    />
        <result property="changeBatterySn"    column="change_battery_sn"    />
        <result property="changeBatteryPower"    column="change_battery_power"    />
        <result property="placeBatteryCompartmentNo"    column="place_battery_compartment_no"    />
        <result property="exchangeBatterySn"    column="exchange_battery_sn"    />
        <result property="exchangeBatteryPower"    column="exchange_battery_power"    />
        <result property="takeBatteryCompartmentNo"    column="take_battery_compartment_no"    />
        <result property="changeStartTime"    column="change_start_time"    />
        <result property="changeEndTime"    column="change_end_time"    />
    </resultMap>

    <select id="getLastestRecord" resultMap="PwtChangeBatteryRecordResult">
        <include refid="selectPwtChangeBatteryRecordVo"/>
        where change_battery_sn is null order by record_id desc limit 1
    </select>

    <sql id="selectPwtChangeBatteryRecordVo">
        select record_id, vehicle_no, vin, change_battery_sn, change_battery_power, place_battery_compartment_no, exchange_battery_sn, exchange_battery_power, take_battery_compartment_no, change_start_time, change_end_time from pwt_change_battery_record
    </sql>

    <select id="selectPwtChangeBatteryRecordList" parameterType="PwtChangeBatteryRecord" resultMap="PwtChangeBatteryRecordResult">
        <include refid="selectPwtChangeBatteryRecordVo"/>
        <where>  
            <if test="vehicleNo != null  and vehicleNo != ''"> and (vehicle_no like concat('%',#{vehicleNo},'%')  or vin like concat('%',#{vehicleNo},'%'))</if>
            <if test="changeStartTime != null "> and date_format(change_start_time,'%Y-%m-%d') &gt;= date_format(#{changeStartTime},'%Y-%m-%d')</if>
            <if test="changeEndTime != null "> and date_format(change_end_time, '%Y-%m-%d') &lt;= date_format(#{changeEndTime},'%Y-%m-%d')</if>
        </where>
        order by change_start_time desc, record_id desc
    </select>
    
    <select id="selectPwtChangeBatteryRecordById" parameterType="Long" resultMap="PwtChangeBatteryRecordResult">
        <include refid="selectPwtChangeBatteryRecordVo"/>
        where record_id = #{recordId}
    </select>

    <select id="getDayDataInfo" resultType="com.pwt.web.domain.ChangePowerModel">
        SELECT DATE_FORMAT(change_end_time,'%k') AS 'changeTime', (SUM(exchange_battery_power) - SUM(change_battery_power)) AS 'kwh',
        COUNT(DATE_FORMAT(change_end_time,'%H')) AS 'changeCount' FROM pwt_change_battery_record WHERE DATE_FORMAT(change_end_time,'%Y-%m-%d')
        = DATE_FORMAT(SYSDATE(),'%Y-%m-%d') GROUP BY changeTime
    </select>

    <select id="getMonthDataInfo" resultType="com.pwt.web.domain.ChangePowerModel">
        SELECT DATE_FORMAT(change_end_time,'%e') AS 'changeTime', (SUM(exchange_battery_power) - SUM(change_battery_power)) AS 'kwh',
        COUNT(1) AS 'changeCount' FROM pwt_change_battery_record WHERE DATE_FORMAT(change_end_time,'%Y-%m')
        = DATE_FORMAT(SYSDATE(),'%Y-%m') GROUP BY changeTime
    </select>

    <select id="getYearDataInfo" resultType="com.pwt.web.domain.ChangePowerModel">
        SELECT DATE_FORMAT(change_end_time,'%c') AS 'changeTime', (SUM(exchange_battery_power) - SUM(change_battery_power)) AS 'kwh',
        COUNT(1) AS 'changeCount' FROM pwt_change_battery_record WHERE DATE_FORMAT(change_end_time,'%Y')
        = DATE_FORMAT(SYSDATE(),'%Y') GROUP BY changeTime
    </select>

    <select id="getExchangeStatistics" resultType="com.pwt.web.domain.ExchangeStatistics">
        SELECT count(1) as `count`, IFNULL(SUM(exchange_battery_power - change_battery_power),0) as exchangePower FROM pwt_change_battery_record;
    </select>

    <insert id="insertPwtChangeBatteryRecord" parameterType="PwtChangeBatteryRecord" useGeneratedKeys="true" keyProperty="recordId">
        insert into pwt_change_battery_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="vehicleNo != null">vehicle_no,</if>
            <if test="vin != null">vin,</if>
            <if test="changeBatterySn != null">change_battery_sn,</if>
            <if test="changeBatteryPower != null">change_battery_power,</if>
            <if test="placeBatteryCompartmentNo != null">place_battery_compartment_no,</if>
            <if test="exchangeBatterySn != null">exchange_battery_sn,</if>
            <if test="exchangeBatteryPower != null">exchange_battery_power,</if>
            <if test="takeBatteryCompartmentNo != null">take_battery_compartment_no,</if>
            <if test="changeStartTime != null">change_start_time,</if>
            <if test="changeEndTime != null">change_end_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="vehicleNo != null">#{vehicleNo},</if>
            <if test="vin != null">#{vin},</if>
            <if test="changeBatterySn != null">#{changeBatterySn},</if>
            <if test="changeBatteryPower != null">#{changeBatteryPower},</if>
            <if test="placeBatteryCompartmentNo != null">#{placeBatteryCompartmentNo},</if>
            <if test="exchangeBatterySn != null">#{exchangeBatterySn},</if>
            <if test="exchangeBatteryPower != null">#{exchangeBatteryPower},</if>
            <if test="takeBatteryCompartmentNo != null">#{takeBatteryCompartmentNo},</if>
            <if test="changeStartTime != null">#{changeStartTime},</if>
            <if test="changeEndTime != null">#{changeEndTime},</if>
         </trim>
    </insert>

    <update id="updatePwtChangeBatteryRecord" parameterType="PwtChangeBatteryRecord">
        update pwt_change_battery_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="vehicleNo != null">vehicle_no = #{vehicleNo},</if>
            <if test="vin != null">vin = #{vin},</if>
            <if test="changeBatterySn != null">change_battery_sn = #{changeBatterySn},</if>
            <if test="changeBatteryPower != null">change_battery_power = #{changeBatteryPower},</if>
            <if test="placeBatteryCompartmentNo != null">place_battery_compartment_no = #{placeBatteryCompartmentNo},</if>
            <if test="exchangeBatterySn != null">exchange_battery_sn = #{exchangeBatterySn},</if>
            <if test="exchangeBatteryPower != null">exchange_battery_power = #{exchangeBatteryPower},</if>
            <if test="takeBatteryCompartmentNo != null">take_battery_compartment_no = #{takeBatteryCompartmentNo},</if>
            <if test="changeStartTime != null">change_start_time = #{changeStartTime},</if>
            <if test="changeEndTime != null">change_end_time = #{changeEndTime},</if>
        </trim>
        where record_id = #{recordId}
    </update>

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

    <delete id="deletePwtChangeBatteryRecordByIds" parameterType="String">
        delete from pwt_change_battery_record where record_id in
        <foreach item="recordId" collection="array" open="(" separator="," close=")">
            #{recordId}
        </foreach>
    </delete>
    <select id="getChangeCount" resultType="int">
        select count(record_id) from pwt_change_battery_record
        where change_battery_sn = #{batterySn}
    </select>
    
</mapper>