<?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.njcn.gather.tool.addledger.mapper.AddLedgerLineMapper">

    <select id="selectUsedLineNos" resultType="java.lang.Integer">
        SELECT line_no
        FROM cs_line
        WHERE device_id = #{deviceId}
          AND status = 1
        <if test="lineId != null and lineId != ''">
          AND line_id &lt;&gt; #{lineId}
        </if>
          AND line_no BETWEEN 1 AND 20
    </select>

    <select id="countActiveLineNo" resultType="java.lang.Integer">
        SELECT COUNT(1)
        FROM cs_line
        WHERE device_id = #{deviceId}
          AND line_no = #{lineNo}
          AND status = 1
        <if test="lineId != null and lineId != ''">
          AND line_id &lt;&gt; #{lineId}
        </if>
    </select>

    <select id="selectLinePathByLineIds" resultType="com.njcn.gather.tool.addledger.pojo.vo.AddLedgerLinePathVO">
        SELECT engineering.id AS engineeringId,
               engineering.name AS engineeringName,
               project.id AS projectId,
               project.name AS projectName,
               equipment.id AS equipmentId,
               equipment.name AS equipmentName,
               equipment.mac AS equipmentMac,
               line.line_id AS lineId,
               line.name AS lineName,
               line.line_interval AS lineInterval
        FROM cs_line line
                 INNER JOIN cs_equipment_delivery equipment ON equipment.id = line.device_id
                 INNER JOIN cs_project project ON project.id = equipment.associated_project
                 INNER JOIN cs_engineering engineering ON engineering.id = equipment.associated_engineering
        WHERE line.status = 1
          AND equipment.run_status &lt;&gt; 0
          AND project.status = 1
          AND engineering.status = 1
          AND line.line_id IN
        <foreach collection="lineIds" item="lineId" open="(" separator="," close=")">
            #{lineId}
        </foreach>
    </select>

    <select id="selectLinePathByQuery" resultType="com.njcn.gather.tool.addledger.pojo.vo.AddLedgerLinePathVO">
        SELECT engineering.id AS engineeringId,
               engineering.name AS engineeringName,
               project.id AS projectId,
               project.name AS projectName,
               equipment.id AS equipmentId,
               equipment.name AS equipmentName,
               equipment.mac AS equipmentMac,
               line.line_id AS lineId,
               line.name AS lineName,
               line.line_interval AS lineInterval
        FROM cs_line line
                 INNER JOIN cs_equipment_delivery equipment ON equipment.id = line.device_id
                 INNER JOIN cs_project project ON project.id = equipment.associated_project
                 INNER JOIN cs_engineering engineering ON engineering.id = equipment.associated_engineering
        WHERE line.status = 1
          AND equipment.run_status &lt;&gt; 0
          AND project.status = 1
          AND engineering.status = 1
        <if test="param.engineeringName != null and param.engineeringName != ''">
          AND engineering.name LIKE CONCAT('%', #{param.engineeringName}, '%')
        </if>
        <if test="param.projectName != null and param.projectName != ''">
          AND project.name LIKE CONCAT('%', #{param.projectName}, '%')
        </if>
        <if test="param.equipmentName != null and param.equipmentName != ''">
          AND equipment.name LIKE CONCAT('%', #{param.equipmentName}, '%')
        </if>
        <if test="param.lineName != null and param.lineName != ''">
          AND line.name LIKE CONCAT('%', #{param.lineName}, '%')
        </if>
        ORDER BY engineering.name ASC, project.name ASC, equipment.name ASC, line.name ASC
        LIMIT #{param.limit}
    </select>

    <update id="softDeleteByIds">
        UPDATE cs_line
        SET status = 0,
            update_by = #{updateBy},
            update_time = NOW()
        WHERE line_id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>
</mapper>
