<?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="operatorId !=null">
            and xcs.operator_id =#{operatorId}
        </if>
        <if test="chargingStationId !=null">
            and xcs.charging_station_id =#{chargingStationId}
        </if>
        <if test="null != stationName and '' != stationName">
            and xcs.name like concat('%',#{stationName},'%')
        </if>
        <if test="null != tenantId and '' != tenantId">
            and xcs.tenant_id =#{tenantId}
        </if>
        <if test="type==1">
            and xcs.charging_station_id in (select charging_station_id from xhpc_charging_station where operator_id=#{userId})
        </if>
        <if test="type==2">
            and xcs.charging_station_id in (select charging_station_id from xhpc_user_privilege where user_id=#{userId})
        </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,
               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,
               terminal.serial_number as qrContent
        FROM xhpc_terminal AS terminal
                 LEFT JOIN xhpc_img img ON img.terminal_id = terminal.terminal_id
            AND img.del_flag = 0
                 LEFT JOIN xhpc_charging_pile pile ON pile.charging_pile_id = terminal.charging_pile_id
            AND pile.del_flag = 0
                 LEFT JOIN xhpc_charging_station station ON station.charging_station_id = terminal.charging_station_id
            AND station.del_flag = 0
        WHERE terminal.charging_station_id = #{stationId}
          AND terminal.del_flag = 0
        ORDER BY terminal.serial_number ASC

    </select>


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

        SELECT i.url
        FROM xhpc_img AS i
        WHERE i.terminal_id IN
              (SELECT terminal_id FROM xhpc_terminal WHERE charging_pile_id = #{pileId} AND del_flag = 0)
          AND i.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
            AND i.del_flag = 0
        WHERE t.terminal_id IN
              (SELECT terminal_id FROM xhpc_terminal WHERE charging_station_id = #{stationId} AND del_flag = 0)

    </select>

</mapper>