<?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.bcxin.identify.business.dao.LYTrajectoryMapper">

    <!--根据省编码查询最后一条的序号-->
    <select id="getMaxSeqByProvince" resultType="java.lang.Integer">
        SELECT
            seq
          FROM ly_trajectory
         WHERE province =#{province}
           ORDER BY seq DESC LIMIT 1
    </select>

    <!--根据省编码查询可用的key/sid-->
    <select id="getKeyByProvince" resultType="java.util.Map">
        SELECT
            tKey AS 'key',serviceId
          FROM ly_trajectory
         WHERE province =#{province}
           AND isFull = 0 ORDER BY seq LIMIT 1
    </select>

    <!--根据userid、省编码查询key、sid、tid-->
    <select id="getTerminalByUserId" resultType="java.util.Map">
        SELECT
            t2.tKey as 'key',t2.serviceId,t1.tid
          FROM ly_trajectory_terminal t1, ly_trajectory t2
         WHERE t1.lyTrajectoryId = t2.lyTrajectoryId
           AND t1.userid =#{userId}
    </select>

    <select id="terminalCount" resultType="java.lang.Integer">
        SELECT
            count(1)
          FROM ly_trajectory_terminal
         WHERE tid =#{tId}
           AND userid =#{userId}
    </select>

    <select id="groupProvince" resultType="java.lang.String">
        SELECT CONCAT(province,'') FROM ly_trajectory GROUP BY province
    </select>

    <!--根据省编码查询剩余可添加设备数量-->
    <select id="findKeyIsFull" resultType="java.lang.Integer">
        SELECT
            (SELECT
                sum(maxTerminalNum)
                FROM ly_trajectory
                WHERE province = #{province} AND isFull = 0)
            -
            (SELECT
                count(1) a
                FROM ly_trajectory_terminal t1, ly_trajectory t2
            WHERE t1.lyTrajectoryId = t2.lyTrajectoryId
            AND t2.province = #{province} AND t2.isFull = 0)
    </select>

    <insert id="insertBatchTrajectory" >
        INSERT INTO ly_trajectory(
        lyTrajectoryId,
        tKey,
        serviceId,
        name,
        province,
        maxTerminalNum,
        isFull,
        seq,
        createOn)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.lyTrajectoryId},
            #{item.tKey},
            #{item.serviceId},
            #{item.name},
            #{item.province},
            100000,
            0,
            #{item.seq},
            NOW())
        </foreach>
    </insert>

    <insert id="insertTerminal" parameterType="com.bcxin.identify.business.domain.LYTrajectoryTerminal">
        insert into ly_trajectory_terminal
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="lyTrajectoryTerminalId != null">
                lyTrajectoryTerminalId,
            </if>
            <if test="lyTrajectoryId != null">
                lyTrajectoryId,
            </if>
            <if test="userid != null">
                userid,
            </if>
            <if test="tid != null">
                tid,
            </if>
            <if test="createOn != null">
                createOn,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="lyTrajectoryTerminalId != null">
                #{lyTrajectoryTerminalId},
            </if>
            <if test="lyTrajectoryId != null">
                #{lyTrajectoryId},
            </if>
            <if test="userid != null">
                #{userid},
            </if>
            <if test="tid != null">
                #{tid},
            </if>
            <if test="createOn != null">
                #{createOn},
            </if>
        </trim>
    </insert>

    <update id="updateFull">
        UPDATE ly_trajectory
        SET isFull=1
        WHERE province = #{province} AND isFull=0
    </update>
</mapper>