<?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.railway.business.catenary.mapper.BusJlgzMapper">

  <resultMap id="BaseResultMap" type="com.railway.business.catenary.domain.BusJlgz">
    <result column="id" property="id"/>
    <result column="line_id" property="lineId"/>
    <result column="line_name" property="lineName"/>
    <result column="substation_id" property="substationId"/>
    <result column="substation_name" property="substationName"/>
    <result column="xingbie" property="xingbie"/>
    <result column="marker" property="marker"/>
    <result column="yylx" property="yylx"/>
    <result column="operation_date" property="operationDate"/>
    <result column="gzms" property="gzms"/>
    <result column="create_by" property="createBy"/>
    <result column="create_time" property="createTime"/>
    <result column="update_by" property="updateBy"/>
    <result column="update_time" property="updateTime"/>
    <result column="del_flag" property="delFlag"/>
    <result column="yylx_text" property="yylxText"/>
    <result column="xingbie_text" property="xingbieText"/>
  </resultMap>

  <sql id="Base_Column_List">
    t.id,
    v.line_id,
    v.line_name,
    t.substation_id,
    v.substation_name,
    t.xingbie,
    t.marker,
    t.yylx,
    t.operation_date,
    t.gzms,
    t.create_by,
    t.create_time,
    t.update_by,
    t.update_time,
    t.del_flag
  </sql>

  <insert id="insert" parameterType="com.railway.business.catenary.domain.BusJlgz">
    <selectKey keyProperty="id" order="AFTER" resultType="Long">
      select @@IDENTITY as id
    </selectKey>
    INSERT INTO base_jlgz
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test='null != substationId'>
        substation_id,
      </if>
      <if test='null != lineId'>
        line_id,
      </if>
      <if test='null != xingbie'>
        xingbie,
      </if>
      <if test='null != marker'>
        marker,
      </if>
      <if test='null != yylx'>
        yylx,
      </if>
      <if test='null != operationDate'>
        operation_date,
      </if>
      <if test='null != gzms'>
        gzms,
      </if>
      <if test='null != createBy'>
        create_by,
      </if>
      <if test='null != createTime'>
        create_time,
      </if>
      <if test='null != updateBy'>
        update_by,
      </if>
      <if test='null != updateTime'>
        update_time,
      </if>
      <if test='null != delFlag'>
        del_flag
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test='null != substationId'>
        #{substationId},
      </if>
      <if test='null != lineId'>
        #{lineId},
      </if>
      <if test='null != xingbie'>
        #{xingbie},
      </if>
      <if test='null != marker'>
        #{marker},
      </if>
      <if test='null != yylx'>
        #{yylx},
      </if>
      <if test='null != operationDate'>
        #{operationDate},
      </if>
      <if test='null != gzms'>
        #{gzms},
      </if>
      <if test='null != createBy'>
        #{createBy},
      </if>
      <if test='null != createTime'>
        #{createTime},
      </if>
      <if test='null != updateBy'>
        #{updateBy},
      </if>
      <if test='null != updateTime'>
        #{updateTime},
      </if>
      <if test='null != delFlag'>
        #{delFlag}
      </if>
    </trim>
  </insert>

  <delete id="delete">
    UPDATE base_jlgz
    set del_flag='1'
    WHERE id = #{id}
  </delete>

  <update id="update" parameterType="com.railway.business.catenary.domain.BusJlgz">
    UPDATE base_jlgz
    <set>
      <if test='null != substationId'>substation_id = #{substationId},</if>
      <if test='null != lineId'>line_id = #{lineId},</if>
      <if test='null != xingbie'>xingbie = #{xingbie},</if>
      <if test='null != marker'>marker = #{marker},</if>
      <if test='null != yylx'>yylx = #{yylx},</if>
      <if test='null != operationDate'>operation_date = #{operationDate},</if>
      <if test='null != gzms'>gzms = #{gzms},</if>
      <if test='null != updateBy'>update_by = #{updateBy},</if>
      <if test='null != updateTime'>update_time = #{updateTime},</if>
      <if test='null != delFlag'>del_flag = #{delFlag}</if>
    </set>
    WHERE id = #{id}
  </update>


  <select id="getInfo" resultMap="BaseResultMap">
    SELECT
    DISTINCT
    <include refid="Base_Column_List"/> ,
    dict1.dict_label as xingbie_text,
    dict2.dict_label as yylx_text
    FROM
    base_jlgz t
    LEFT JOIN v_substation v ON t.substation_id = v.substation_id and t.line_id = v.line_id
    LEFT JOIN sys_dict_data dict1 ON (t.xingbie = dict1.dict_value AND dict1.dict_type = 'xingbie')
    LEFT JOIN sys_dict_data dict2 ON (t.yylx = dict2.dict_value AND dict2.dict_type = 'gzyy')
    <where>
        t.del_flag = '0'
      <if test="id!=null and id!=''">
        and t.id = #{id}
      </if>
      <if test="lineId!=null and lineId!=''">
        AND v.line_id = #{lineId}
      </if>
    </where>
  </select>

  <select id="getList" resultMap="BaseResultMap">
    SELECT
    DISTINCT
    dict1.dict_label as xingbie_text,
    dict2.dict_label as yylx_text,
    <include refid="Base_Column_List"/>
    FROM
    base_jlgz t
    LEFT JOIN v_substation v ON t.substation_id = v.substation_id and t.line_id = v.line_id
    LEFT JOIN sys_dict_data dict1 ON (t.xingbie = dict1.dict_value AND dict1.dict_type = 'xingbie')
    LEFT JOIN sys_dict_data dict2 ON (t.yylx = dict2.dict_value AND dict2.dict_type = 'gzyy')
    <where>
      t.del_flag = '0'
      AND dict1.status =  '0'
      AND dict2.status =  '0'
      <if test="lineId!=null and lineId!=''">
        AND v.line_id = #{lineId}
      </if>
      <if test="substationId!=null and substationId!=''">
        and t.substation_id=#{substationId}
      </if>
      <if test="xingbie!=null and xingbie!=''">
        and t.xingbie=#{xingbie}
      </if>
      <if test="marker!=null and marker!=''">
        AND (t.marker + 0.0) BETWEEN (#{marker} + 0.0) - (#{pillarArea} + 0.0)
        AND (#{marker} + 0.0) + (#{pillarArea} + 0.0)
      </if>
      <if test="yylxs!=null and yylxs.length > 0">
        and t.yylx in
        <foreach collection="yylxs" item="yylx" open="(" separator="," close=")">
          #{yylx}
        </foreach>
      </if>
      <if test="operationDate!=null and operationDate!=''">
        and STR_TO_DATE(t.operation_date,'%Y-%m-%d')=#{operationDate}
      </if>
      <if test="gzms!=null and gzms!=''">
        and t.gzms=#{gzms}
      </if>
      <if test="createBy!=null and createBy!=''">
        and t.create_by=#{createBy}
      </if>
      <if test="createTime!=null">
        and DATE_FORMAT(t.create_time, '%Y-%m-%d') = #{createTime}
      </if>
      <if test="updateBy!=null and updateBy!=''">
        and t.update_by=#{updateBy}
      </if>
      <if test="updateTime!=null">
        and DATE_FORMAT(t.update_time, '%Y-%m-%d') = #{updateTime}
      </if>
      <if test="delFlag!=null and delFlag!=''">
        and t.del_flag=#{delFlag}
      </if>
    </where>
    <if test="orderBy!=null and orderBy!=''">
      order by STR_TO_DATE(t.${orderBy},'%Y-%m-%d')
    </if>
    <if test="isAsc!=null and isAsc!=''">
      <if test="isAsc == '1'.toString()">
        asc
      </if>
      <if test="isAsc == '0'.toString()">
        desc
      </if>
    </if>
  </select>

  <select id="getListByZz" resultType="com.railway.business.catenary.domain.vo.BusJlgzListByZzVo">
    SELECT dictYylx.dict_value as yylx, dictYylx.dict_label as yylxText, ifnull(res.total,0) as total FROM
    (
      SELECT jlgz.yylx, jlgz.yylx_text as yylxText, count(1) as total FROM
      (
          SELECT
            dict2.dict_label as yylx_text,
            t.yylx
          FROM base_jlgz t
          LEFT JOIN v_substation v ON t.substation_id = v.substation_id and t.line_id = v.line_id
          LEFT JOIN sys_dict_data dict2 ON (t.yylx = dict2.dict_value AND dict2.dict_type = 'gzyy')
          WHERE t.del_flag = '0'
          <if test="query.lineId != null and query.lineId != ''">
            AND v.line_id = #{query.lineId}
          </if>
          <if test="query.xingbie != null and query.xingbie != ''">
            AND t.xingbie = #{query.xingbie}
          </if>
          <if test="query.marker != null and query.marker != ''">
            AND (t.marker + 0.0) BETWEEN (#{query.marker} + 0.0) - (#{query.pillarArea} + 0.0)
            AND (#{query.marker} + 0.0) + (#{query.pillarArea} + 0.0)
          </if>
      ) jlgz
      GROUP BY jlgz.yylx
    ) res right join sys_dict_data dictYylx on res.yylx = dictYylx.dict_value
    where dictYylx.dict_type = 'gzyy'
    order by ifnull(res.total,0) desc, dictYylx.dict_sort
  </select>
</mapper>