<?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.shkj.wcs.mapper.BasePointMapper">

    <resultMap type="BasePoint" id="BasePointResult">
        <result property="id"    column="id"    />
        <result property="pointNo"    column="point_no"    />
        <result property="pointName"    column="point_name"    />
        <result property="pointGroup"    column="point_group"    />
        <result property="tunnel"    column="tunnel"    />
        <result property="isActive"    column="is_active"    />
        <result property="isOccupy"    column="is_occupy"    />
        <result property="errDesc"    column="err_desc"    />
        <result property="errStatus"    column="err_status"    />
        <result property="plcId"    column="plc_Id"    />
    </resultMap>

    <sql id="selectBasePointVo">
        select id, point_no, point_name, point_group, tunnel, is_active, is_occupy, err_desc, err_status, create_time, update_time, create_by, update_by from base_point
    </sql>

    <select id="selectBasePointList" parameterType="BasePoint" resultType="com.shkj.wcs.vo.BasePointVo">
        select point.* ,pointgroup.group_name,wcs_device.dev_name from  base_point point
        left join base_point_group pointgroup on point.point_group=pointgroup.id
        left join wcs_device on point.dev_code=wcs_device.id
        <where>
            <if test="pointNo != null  and pointNo != ''"> and point_no = #{pointNo}</if>
            <if test="pointName != null  and pointName != ''"> and point_name like concat('%', #{pointName}, '%')</if>
            <if test="pointGroup != null "> and point_group = #{pointGroup}</if>
            <if test="tunnel != null  and tunnel != ''"> and tunnel = #{tunnel}</if>
            <if test="isActive != null  and isActive != ''"> and is_active = #{isActive}</if>
            <if test="isOccupy != null  and isOccupy != ''"> and is_occupy = #{isOccupy}</if>
            <if test="errDesc != null  and errDesc != ''"> and err_desc = #{errDesc}</if>
            <if test="errStatus != null  and errStatus != ''"> and err_status = #{errStatus}</if>
        </where>
    </select>

    <select id="selectBasePointById" parameterType="Long" resultMap="BasePointResult">
        <include refid="selectBasePointVo"/>
        where id = #{id}
    </select>

    <select id="selectBasePointByPointNo" parameterType="String" resultMap="BasePointResult">
        <include refid="selectBasePointVo"/>
        where point_no = #{pointNo}
        limit 1
    </select>



    <insert id="insertBasePoint" parameterType="BasePoint" useGeneratedKeys="true" keyProperty="id">
        insert into base_point
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="pointNo != null">point_no,</if>
            <if test="pointName != null">point_name,</if>
            <if test="pointGroup != null">point_group,</if>
            <if test="tunnel != null">tunnel,</if>
            <if test="isActive != null">is_active,</if>
            <if test="isOccupy != null">is_occupy,</if>
            <if test="errDesc != null">err_desc,</if>
            <if test="errStatus != null">err_status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="pointNo != null">#{pointNo},</if>
            <if test="pointName != null">#{pointName},</if>
            <if test="pointGroup != null">#{pointGroup},</if>
            <if test="tunnel != null">#{tunnel},</if>
            <if test="isActive != null">#{isActive},</if>
            <if test="isOccupy != null">#{isOccupy},</if>
            <if test="errDesc != null">#{errDesc},</if>
            <if test="errStatus != null">#{errStatus},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
         </trim>
    </insert>

    <update id="updateBasePoint" parameterType="BasePoint">
        update base_point
        <trim prefix="SET" suffixOverrides=",">
            <if test="pointNo != null">point_no = #{pointNo},</if>
            <if test="pointName != null">point_name = #{pointName},</if>
            <if test="pointGroup != null">point_group = #{pointGroup},</if>
            <if test="tunnel != null">tunnel = #{tunnel},</if>
            <if test="isActive != null">is_active = #{isActive},</if>
            <if test="isOccupy != null">is_occupy = #{isOccupy},</if>
            <if test="errDesc != null">err_desc = #{errDesc},</if>
            <if test="errStatus != null">err_status = #{errStatus},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteBasePointById" parameterType="Long">
        delete from base_point where id = #{id}
    </delete>

    <delete id="deleteBasePointByIds" parameterType="String">
        delete from base_point where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectBasePointByQuery" parameterType="com.shkj.wcs.bo.BasePointQueryBo" resultType="com.shkj.wcs.vo.BasePointVo">
        select base_point.id,base_point.point_no,base_point.point_name,base_point.tunnel,base_point_group.group_no,base_point_group.group_name
        ,base_point_group.group_type,base_point_group.is_down,base_point.dev_code ,
        wcs_plc_connect.`port`,wcs_plc_connect.point_tables,wcs_plc_connect.plc_protocol,wcs_plc_connect.plc_ip,wcs_plc_connect.plc_factory,plc_type,
        wcs_plc_connect.id as plcId
        from base_point
        inner join base_point_group on base_point.point_group=base_point_group.id
        left join wcs_device on wcs_device.id = base_point.dev_code
        left join wcs_plc_connect on wcs_plc_connect.dev_code = base_point.dev_code
        where ifnull(base_point.is_active,'1')='1' and ifnull(base_point.is_occupy,'0')='0'

        <if test="pointNo != null  and pointNo != ''"> and point_no = #{pointNo}</if>
        <if test="pointName != null  and pointName != ''"> and point_name like concat('%', #{pointName}, '%')</if>
        <if test="groupNo != null "> and base_point_group.group_no = #{groupNo}</if>
        <if test="tunnel != null  and tunnel != ''"> and tunnel = #{tunnel}</if>
        <if test="devCode != null  and devCode != ''"> and dev_code = #{devCode}</if>
        <if test="devDirection != null  and devDirection != ''"> and dev_direction = #{devDirection}</if>
        <if test="devType != null  and devType != ''"> and wcs_device.dev_type = #{devType}</if>
        limit 1
    </select>

    <select id="selectBasePoint" parameterType="BasePoint" resultMap="BasePointResult">
        select * from  base_point point
        <where>
            <if test="pointNo != null  and pointNo != ''"> and point_no = #{pointNo}</if>
            <if test="pointName != null  and pointName != ''"> and point_name like concat('%', #{pointName}, '%')</if>
            <if test="pointGroup != null "> and point_group = #{pointGroup}</if>
            <if test="tunnel != null  and tunnel != ''"> and tunnel = #{tunnel}</if>
            <if test="isActive != null  and isActive != ''"> and is_active = #{isActive}</if>
            <if test="isOccupy != null  and isOccupy != ''"> and is_occupy = #{isOccupy}</if>
            <if test="errDesc != null  and errDesc != ''"> and err_desc = #{errDesc}</if>
            <if test="errStatus != null  and errStatus != ''"> and err_status = #{errStatus}</if>
        </where>
        limit 1
    </select>

</mapper>
