<?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.pqs9900.mapper.linedata.LineMarkWeightMapper">

    <select id="getLineMarkWeight" resultType="com.pqs9900.pojo.linedata.LineMarkWeightVo">
        SELECT
        j.dic_name province,
        a.line_index lineIndex,
        a.name lineName,
        k.dic_name lineGrade,
        d.name devName,
        d.status,
        d.devflag devFlag,
        e.name subName,
        f.name gdName,
        h.COMMUN_FEE_MARK,
        h.ASSET_BELONG,
        h.SERVICE_MARK,
        h.AGENT_MARK,
        h.COMPANY_MARK,
        h.USER_MARK,
        h.LINE_TYPE_MARK,
        h.BUSINESS_MARK
        FROM
        pq_line a
        LEFT JOIN pq_linedetail b ON a.line_index = b.line_index
        LEFT JOIN PQS_LINE_WEIGHT h on a.line_index = h.line_index
        LEFT JOIN pq_device d ON a.dev_index = d.dev_index
        LEFT JOIN pq_substation e ON a.sub_index = e.sub_index
        LEFT JOIN pq_gdinformation f ON a.gd_index = f.gd_index
        LEFT JOIN pqs_dicdata k ON b.line_grade = k.dic_index
        LEFT JOIN pq_province i ON f.province_index = i.province_index
        LEFT JOIN pqs_dicdata j ON i.name = j.dic_index
        where d.devmodel = 1
        <if test="devFalg!=null">
            and d.DEVFLAG = #{devFalg}
        </if>
        <if test="lineGrade!=null">
            and k.dic_leave = #{lineGrade}
        </if>
        order by i.province_index,f.gd_index,e.sub_index,d.dev_index,a.line_index
    </select>

    <insert id="singleOperate">
        insert into PQS_LINE_WEIGHT (LINE_INDEX,COMMUN_FEE_MARK,ASSET_BELONG, SERVICE_MARK,AGENT_MARK,COMPANY_MARK,USER_MARK,LINE_TYPE_MARK,BUSINESS_MARK)
        values (
            #{item.lineIndex},#{item.communFeeMark},#{item.assetBelong},
            #{item.serviceMark},#{item.agentMark},#{item.companyMark},#{item.userMark},#{item.lineTypeMark},#{item.businessMark}
            )
    </insert>

    <select id="getLineGrade" resultType="com.njcn.pojo.pointInfo.LineLedger">
        select
        a.line_index lineIndex,
        b.line_grade lineGrade
        from pq_line a
        left join pq_linedetail b on a.line_index = b.line_index
        where a.line_index=#{lineIndex}
    </select>

    <select id="getLineMarkData" resultType="com.pqs9900.pojo.linedata.MarkWeightExcel">
        SELECT
        a.line_index lineIndex,
        a.name lineName,
        d.name devName,
        d.dev_index devIndex,
        e.name subName,
        f.name gdName
        FROM
        pq_line a
        LEFT JOIN pq_device d ON a.dev_index = d.dev_index
        LEFT JOIN pq_substation e ON a.sub_index = e.sub_index
        LEFT JOIN pq_gdinformation f ON a.gd_index = f.gd_index
        where d.devmodel = 1
        and f.name = #{gdName}
        and e.name = #{subName}
        and d.name = #{devName}
        and a.name = #{lineName}
    </select>



    <insert id="batchAddLineWeight" parameterType="java.util.List">
        insert into PQS_LINE_WEIGHT
        <foreach collection="list" item="item" index="index" separator="UNION ALL">
            select
            #{item.lineIndex},#{item.communFeeMark},#{item.assetBelong},
            #{item.serviceMark},#{item.agentMark},#{item.companyMark},#{item.userMark},#{item.lineTypeMark},#{item.businessMark}
            from dual
        </foreach>
    </insert>

    <delete id="batchDelLineWeight">
        delete from PQS_LINE_WEIGHT where line_index in
        <foreach collection="lineIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

    <update id="batchUpdateNode" parameterType="java.util.List">
        <foreach collection="list" item="MarkWeightExcel" index="index" open="begin" close=";end;" separator=";">
            update pq_device set
            node_index= #{MarkWeightExcel.markLeaveId}
            where dev_index = #{MarkWeightExcel.devIndex}
        </foreach>
    </update>


    <select id="getStopLineIds" resultType="integer">
        select a.LINE_INDEX from PQ_LINE a
        inner join PQ_LINEDETAIL detail on a.LINE_INDEX = detail.LINE_INDEX
        inner join PQ_DEVICE b on a.DEV_INDEX = b.DEV_INDEX
        where b.DEVFLAG = 2
        and detail.LINE_GRADE is not null
    </select>


</mapper>
