<?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.xhpc.activity.mapper.XhpcDeviceMessageMapper">


    <delete id="deleteMessageByDate">
        delete from xhpc_device_message where create_time <![CDATA[ <=  ]]> #{date}
    </delete>


    <select id="selectListByTypeAndSerialNumber" resultType="map">
        select
            device_message_id as 'deviceMessageId',
            type as 'type',
            serial_number as 'serialNumber',
            content as 'content',
            charge_order_no as 'chargeOrderNo',
            status as 'status',
            remark as 'remark',
            create_time as 'createTime',
            create_by as 'createBy',
            update_time as 'updateTime',
            update_by as 'updateBy'
        from xhpc_device_message
        where type=#{type} and serial_number=#{serialNumber}
        <if test="startTime !=null and startTime!=''">
            and create_time <![CDATA[ >=  ]]> #{startTime}
        </if>
        <if test="endTime !=null and endTime!=''">
            and create_time <![CDATA[ <=  ]]> #{endTime}
        </if>
        order by create_time desc
    </select>



    <select id="selectListByChargeOrderNo" resultType="map">
        select
        device_message_id as 'deviceMessageId',
        type as 'type',
        serial_number as 'serialNumber',
        content as 'content',
        charge_order_no as 'chargeOrderNo',
        status as 'status',
        remark as 'remark',
        replace(create_time, 'T', ' ') as 'createTime',
        create_by as 'createBy',
        replace(update_time, 'T', ' ') as 'updateTime',
        update_by as 'updateBy'
        from xhpc_device_message
        where charge_order_no=#{chargeOrderNo}
        order by create_time desc
    </select>


    <select id="selectListByParams" resultType="java.util.Map">
        select
            concat(cp.name,'号桩') as 'chargingPileName',
                st.name as 'chargingStationName',
                cp.brand_model as 'brandModel',
                cp.type as 'pileType',
                cp.power as 'power',
                cp.serial_number as 'serialNumber',
                m.device_message_id as 'deviceMessageId',
                m.type as 'type',
                m.serial_number as 'serialNumber',
                m.content as 'content',
                m.charge_order_no as 'chargeOrderNo',
                m.status as 'status',
                m.remark as 'remark',
                m.create_time as 'createTime',
                m.create_by as 'createBy',
                m.update_time as 'updateTime'
        from xhpc_device_message m
        left join xhpc_charging_pile cp on cp.serial_number=m.serial_number
        left join xhpc_charging_station st on st.charging_station_id =cp.charging_station_id
        where cp.del_flag =0
        <if test="params.number ==1">
            and cp.charging_station_id in(select charging_station_id from xhpc_charging_station where operator_id=#{params.operatorId})
        </if>
        <if test="params.number ==2">
            and cp.charging_station_id in(select charging_station_id from xhpc_user_privilege where user_id=#{params.operatorId})
        </if>
        <if test="params.type!=null and params.type!=''">
            and m.type=#{params.type}
        </if>
        <if test="params.pileId!=null and params.pileId!=''">
            and m.serial_number=#{params.pileId}
        </if>
        <if test="params.tenantId != null and params.tenantId != ''">
            and cp.tenant_id = #{params.tenantId}
        </if>
        <if test="params.serialNumber !=null and params.serialNumber!=''">
            and cp.serial_number=#{params.serialNumber}
        </if>
        <if test="params.chargeOrderNo !=null and params.chargeOrderNo!=''">
            and m.charge_order_no=#{params.chargeOrderNo}
        </if>
        <if test="params.startTime !=null and params.startTime!=''">
            and m.create_time <![CDATA[ >=  ]]> #{params.startTime}
        </if>
        <if test="params.endTime !=null and params.endTime!=''">
            and m.create_time <![CDATA[ <=  ]]> #{params.endTime}
        </if>
        order by m.create_time desc

    </select>


    <select id="getOrderMessageByParams" resultType="java.util.Map">
        select
        co.serial_number as 'serialNumber',
        co.source as 'source',
        case co.source when 0 then 'C端用户'
        when 1 then '流量方用户'
        when 2 then '社区用户'
        when 3 then 'B端用户' end as 'sourceName',
        co.start_time as 'startTime',
        co.end_time as 'endTime',
        co.charging_degree as 'chargingDegree',
        co.amount_charged as 'amountCharged',
        co.charging_time as 'chargingTime',
        co.start_soc as 'startSOc',
        co.end_soc as 'endSoc',
        m.device_message_id as 'messageId',
        m.charge_order_no as 'chargeOrderNo',
        m.content as 'content',
        m.status as 'status',
        m.remark as 'remark',
        m.create_time as 'createTime'
        from xhpc_device_message m
        LEFT JOIN xhpc_charge_order as co on co.serial_number = m.charge_order_no
        left join xhpc_charging_station as st on st.charging_station_id = co.charging_station_id
        where co.del_flag =0
        <if test="params.number !=0 and params.number ==1">
            and co.charging_station_id in(select charging_station_id from xhpc_charging_station where operator_id=#{params.operatorId})
        </if>
        <if test="params.number !=0 and params.number ==2">
            and co.charging_station_id in(select charging_station_id from xhpc_user_privilege where user_id=#{params.operatorId})
        </if>
        <if test="params.tenantId != null and params.tenantId != ''">
            and co.tenant_id = #{params.tenantId}
        </if>
        <if test="params.serialNumber !=null and params.serialNumber !=''">
            and co.serial_number=#{params.serialNumber}
        </if>
        <if test="params.orderId !=null and params.orderId !=''">
            and m.charge_order_no=#{params.orderId}
        </if>
        order by m.charge_order_no asc, m.create_time asc
    </select>

</mapper>
