<?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.del_flag = 0 and xt.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,
               concat('https://www.scxhua.cn?pNum=', t.serial_number) AS qrContent
        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}
          AND p.del_flag = 0
          AND t.del_flag = 0

    </select>

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

        SELECT terminal.NAME          AS tName,
               station.NAME           AS sName,
               terminal.serial_number AS tSN,
               pile.serial_number     AS psn,
               pile.type,
               pile.power,
               concat(
                       'https://www.scxhua.cn?pNum=',
                       terminal.serial_number
                   )                  AS qrContent
        FROM xhpc_img AS img
                 INNER JOIN xhpc_terminal AS terminal ON img.terminal_id = terminal.terminal_id
                 INNER JOIN xhpc_charging_pile AS pile ON pile.charging_pile_id = terminal.charging_pile_id
                 INNER JOIN xhpc_charging_station AS station ON station.charging_station_id = pile.charging_station_id
        WHERE station.charging_station_id = #{stationId}
          AND terminal.del_flag = 0
          AND pile.del_flag = 0

    </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}
        )
          AND t.del_flag = 0

    </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}
        )
          AND t.del_flag = 0

    </select>

</mapper>