<?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.charging.station.mapper.XhpcDownCodeMapper">

    <select id="selectStationsBy" resultType="map">

        select xcs.charging_station_id as chargingStationId, xcs.name as chargingStationName,
        xo.name as operatorName, count(xt.terminal_id) as number
        from xhpc_charging_station as xcs left join xhpc_operator as xo
        on xcs.operator_id = xo.operator_id
        left join xhpc_terminal as xt
        on xcs.charging_station_id = xt.charging_station_id
        where xo.status = 0 and xo.del_flag = 0
        <if test="null != stationName and '' != stationName">
            and xcs.name like concat('%',#{stationName},'%')
        </if>
        group by xcs.charging_station_id, xcs.name, xo.name
    </select>

    <select id="selectPilesBy" resultType="map">

        select xcp.charging_pile_id as pileId, xcp.name as pileName, count(xt.terminal_id) as number
        from xhpc_charging_pile as xcp left join xhpc_terminal as xt
        on xcp.charging_pile_id = xt.charging_pile_id
        where xcp.charging_station_id = #{stationId} and xt.del_flag = 0
        group by pileId, pileName
    </select>

    <select id="selectExcelDataByPileId" resultType="map">

        select t.name as tName, s.name as sName, t.serial_number as tSN, p.serial_number as psn,
        p.type, p.power
        from xhpc_terminal as t left join xhpc_charging_station as s
        on t.charging_station_id = s.charging_station_id
        left join xhpc_charging_pile as p
        on p.charging_pile_id = t.charging_pile_id
        where p.charging_pile_id = #{pileId}
    </select>

    <select id="selectExcelDataByStationId" resultType="map">

        select t.name as tName, s.name as sName, t.serial_number as tSN, p.serial_number as psn,
        p.type, p.power
        from xhpc_terminal as t left join xhpc_charging_station as s
        on t.charging_station_id = s.charging_station_id
        left join xhpc_charging_pile as p
        on p.charging_pile_id = t.charging_pile_id
        where s.charging_station_id = #{stationId}
    </select>


    <select id="selectUrlsByPile" resultType="java.lang.String">

        select i.url
        from xhpc_img as i left join xhpc_terminal as t
        on i.terminal_id = t.terminal_id
        where t.terminal_id in (
            select terminal_id
            from xhpc_terminal
            where charging_pile_id = #{pileId}
        )
    </select>

    <select id="selectUrlsByStation" resultType="java.lang.String">

        select i.url
        from xhpc_img as i left join xhpc_terminal as t
        on i.terminal_id = t.terminal_id
        where t.terminal_id in (
            select terminal_id
            from xhpc_terminal
            where charging_station_id = #{stationId}
        )
    </select>

</mapper>