<?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.BusJcebGjsfxMapper">

  <resultMap id="BaseResultMap" type="com.railway.business.catenary.domain.BusJcebGjsfx">
    <result column="id" property="id"/>
    <result column="station_id" property="stationId"/>
    <result column="station_name" property="stationName"/>
    <result column="dept_station_id" property="deptStationId"/>
    <result column="pillar_start" property="pillarStart"/>
    <result column="pillar_end" property="pillarEnd"/>
    <result column="operation_date" property="operationDate"/>
    <result column="lmdjcspjl_zhz1" property="lmdjcspjlZhz1"/>
    <result column="lmdjcspjl_zhz2" property="lmdjcspjlZhz2"/>
    <result column="lmdjcspjl_zxz2" property="lmdjcspjlZxz2"/>
    <result column="lmdjcspjl_zhz3" property="lmdjcspjlZhz3"/>
    <result column="lmdjcspjl_zhz4" property="lmdjcspjlZhz4"/>
    <result column="lmdjcczjl_zhz1" property="lmdjcczjlZhz1"/>
    <result column="lmdjcczjl_zhz2" property="lmdjcczjlZhz2"/>
    <result column="lmdjcczjl_zxz2" property="lmdjcczjlZxz2"/>
    <result column="lmdjcczjl_zhz3" property="lmdjcczjlZhz3"/>
    <result column="lmdjcczjl_zhz4" property="lmdjcczjlZhz4"/>
    <result column="kgyxdcljzt" property="kgyxdcljzt"/>
    <result column="jyjsffhbz" property="jyjsffhbz"/>
    <result column="xmzjcxcztyyggjcctg" property="xmzjcxcztyyggjcctg"/>
    <result column="operator" property="operator"/>
    <result column="remark" property="remark"/>
    <result column="submit_state" property="submitState"/>
    <result column="del_flag" property="delFlag"/>
    <result column="create_by" property="createBy"/>
    <result column="create_time" property="createTime"/>
    <result column="update_by" property="updateBy"/>
    <result column="update_time" property="updateTime"/>
  </resultMap>

  <sql id="Base_Column_List">
    t.id,
    v.station_id,
    t.dept_station_id,
    v.station_name,
    t.pillar_start,
    t.pillar_end,
    t.operation_date,
    t.lmdjcspjl_zhz1,
    t.lmdjcspjl_zhz2,
    t.lmdjcspjl_zxz2,
    t.lmdjcspjl_zhz3,
    t.lmdjcspjl_zhz4,
    t.lmdjcczjl_zhz1,
    t.lmdjcczjl_zhz2,
    t.lmdjcczjl_zxz2,
    t.lmdjcczjl_zhz3,
    t.lmdjcczjl_zhz4,
    t.kgyxdcljzt,
    t.jyjsffhbz,
    t.xmzjcxcztyyggjcctg,
    t.operator,
    t.remark,
    t.submit_state,
    t.del_flag,
    t.create_by,
    t.create_time,
    t.update_by,
    t.update_time
  </sql>

  <insert id="insert" parameterType="com.railway.business.catenary.domain.BusJcebGjsfx">
    <selectKey keyProperty="id" order="AFTER" resultType="Long">
      select @@IDENTITY as id
    </selectKey>
    INSERT INTO bus_jceb_gjsfx
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test='null != deptStationId'>
        dept_station_id,
      </if>
      <if test='null != pillarStart'>
        pillar_start,
      </if>
      <if test='null != pillarEnd'>
        pillar_end,
      </if>
      <if test='null != operationDate'>
        operation_date,
      </if>
      <if test='null != lmdjcspjlZhz1'>
        lmdjcspjl_zhz1,
      </if>
      <if test='null != lmdjcspjlZhz2'>
        lmdjcspjl_zhz2,
      </if>
      <if test='null != lmdjcspjlZxz2'>
        lmdjcspjl_zxz2,
      </if>
      <if test='null != lmdjcspjlZhz3'>
        lmdjcspjl_zhz3,
      </if>
      <if test='null != lmdjcspjlZhz4'>
        lmdjcspjl_zhz4,
      </if>
      <if test='null != lmdjcczjlZhz1'>
        lmdjcczjl_zhz1,
      </if>
      <if test='null != lmdjcczjlZhz2'>
        lmdjcczjl_zhz2,
      </if>
      <if test='null != lmdjcczjlZxz2'>
        lmdjcczjl_zxz2,
      </if>
      <if test='null != lmdjcczjlZhz3'>
        lmdjcczjl_zhz3,
      </if>
      <if test='null != lmdjcczjlZhz4'>
        lmdjcczjl_zhz4,
      </if>
      <if test='null != kgyxdcljzt'>
        kgyxdcljzt,
      </if>
      <if test='null != jyjsffhbz'>
        jyjsffhbz,
      </if>
      <if test='null != xmzjcxcztyyggjcctg'>
        xmzjcxcztyyggjcctg,
      </if>
      <if test='null != operator'>
        operator,
      </if>
      <if test='null != remark'>
        remark,
      </if>
      <if test='null != submitState'>
        submit_state,
      </if>
      <if test='null != delFlag'>
        del_flag,
      </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>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test='null != deptStationId'>
        #{deptStationId},
      </if>
      <if test='null != pillarStart'>
        #{pillarStart},
      </if>
      <if test='null != pillarEnd'>
        #{pillarEnd},
      </if>
      <if test='null != operationDate'>
        #{operationDate},
      </if>
      <if test='null != lmdjcspjlZhz1'>
        #{lmdjcspjlZhz1},
      </if>
      <if test='null != lmdjcspjlZhz2'>
        #{lmdjcspjlZhz2},
      </if>
      <if test='null != lmdjcspjlZxz2'>
        #{lmdjcspjlZxz2},
      </if>
      <if test='null != lmdjcspjlZhz3'>
        #{lmdjcspjlZhz3},
      </if>
      <if test='null != lmdjcspjlZhz4'>
        #{lmdjcspjlZhz4},
      </if>
      <if test='null != lmdjcczjlZhz1'>
        #{lmdjcczjlZhz1},
      </if>
      <if test='null != lmdjcczjlZhz2'>
        #{lmdjcczjlZhz2},
      </if>
      <if test='null != lmdjcczjlZxz2'>
        #{lmdjcczjlZxz2},
      </if>
      <if test='null != lmdjcczjlZhz3'>
        #{lmdjcczjlZhz3},
      </if>
      <if test='null != lmdjcczjlZhz4'>
        #{lmdjcczjlZhz4},
      </if>
      <if test='null != kgyxdcljzt'>
        #{kgyxdcljzt},
      </if>
      <if test='null != jyjsffhbz'>
        #{jyjsffhbz},
      </if>
      <if test='null != xmzjcxcztyyggjcctg'>
        #{xmzjcxcztyyggjcctg},
      </if>
      <if test='null != operator'>
        #{operator},
      </if>
      <if test='null != remark'>
        #{remark},
      </if>
      <if test='null != submitState'>
        #{submitState},
      </if>
      <if test='null != delFlag'>
        #{delFlag},
      </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>
    </trim>
  </insert>

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

  <update id="update" parameterType="com.railway.business.catenary.domain.BusJcebGjsfx">
    UPDATE bus_jceb_gjsfx
    <set>
      <if test='null != deptStationId'>dept_station_id = #{deptStationId},</if>
      <if test='null != pillarStart'>pillar_start = #{pillarStart},</if>
      <if test='null != pillarEnd'>pillar_end = #{pillarEnd},</if>
      <if test='null != operationDate'>operation_date = #{operationDate},</if>
      <if test='null != lmdjcspjlZhz1'>lmdjcspjl_zhz1 = #{lmdjcspjlZhz1},</if>
      <if test='null != lmdjcspjlZhz2'>lmdjcspjl_zhz2 = #{lmdjcspjlZhz2},</if>
      <if test='null != lmdjcspjlZxz2'>lmdjcspjl_zxz2 = #{lmdjcspjlZxz2},</if>
      <if test='null != lmdjcspjlZhz3'>lmdjcspjl_zhz3 = #{lmdjcspjlZhz3},</if>
      <if test='null != lmdjcspjlZhz4'>lmdjcspjl_zhz4 = #{lmdjcspjlZhz4},</if>
      <if test='null != lmdjcczjlZhz1'>lmdjcczjl_zhz1 = #{lmdjcczjlZhz1},</if>
      <if test='null != lmdjcczjlZhz2'>lmdjcczjl_zhz2 = #{lmdjcczjlZhz2},</if>
      <if test='null != lmdjcczjlZxz2'>lmdjcczjl_zxz2 = #{lmdjcczjlZxz2},</if>
      <if test='null != lmdjcczjlZhz3'>lmdjcczjl_zhz3 = #{lmdjcczjlZhz3},</if>
      <if test='null != lmdjcczjlZhz4'>lmdjcczjl_zhz4 = #{lmdjcczjlZhz4},</if>
      <if test='null != kgyxdcljzt'>kgyxdcljzt = #{kgyxdcljzt},</if>
      <if test='null != jyjsffhbz'>jyjsffhbz = #{jyjsffhbz},</if>
      <if test='null != xmzjcxcztyyggjcctg'>xmzjcxcztyyggjcctg = #{xmzjcxcztyyggjcctg},</if>
      <if test='null != operator'>operator = #{operator},</if>
      <if test='null != remark'>remark = #{remark},</if>
      <if test='null != submitState'>submit_state = #{submitState},</if>
      <if test='null != delFlag'>del_flag = #{delFlag},</if>
      <if test='null != updateBy'>update_by = #{updateBy},</if>
      <if test='null != updateTime'>update_time = #{updateTime}</if>
    </set>
    WHERE id = #{id}
  </update>


  <select id="getInfo" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List"/>
    FROM bus_jceb_gjsfx t
    LEFT JOIN v_station v on t.dept_station_id = v.dept_station_id
    WHERE t.del_flag='0' and t.id = #{id}

  </select>

  <select id="getList" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List"/>
    FROM bus_jceb_gjsfx t
    LEFT JOIN v_station v on t.dept_station_id = v.dept_station_id
    <where>
      t.del_flag='0'
      <if test="deptId!=null and deptId!=''">
        and v.dept_id=#{deptId}
      </if>
      <if test="stationId!=null and stationId!=''">
        and v.station_id=#{stationId}
      </if>
      <if test="stationIds!=null and stationIds.length > 0">
        and v.station_id in
        <foreach collection="stationIds" item="stationId" index="i" open="(" separator="," close=")">
          #{stationId}
        </foreach>
      </if>
      <if test="pillarStart!=null and pillarStart!=''">
        and t.pillar_start=#{pillarStart}
      </if>
      <if test="pillarEnd!=null and pillarEnd!=''">
        and t.pillar_end=#{pillarEnd}
      </if>
      <if test="operationDate!=null">
        and t.operation_date=#{operationDate}
      </if>
      <if test="lmdjcspjlZhz1!=null and lmdjcspjlZhz1!=''">
        and t.lmdjcspjl_zhz1=#{lmdjcspjlZhz1}
      </if>
      <if test="lmdjcspjlZhz2!=null and lmdjcspjlZhz2!=''">
        and t.lmdjcspjl_zhz2=#{lmdjcspjlZhz2}
      </if>
      <if test="lmdjcspjlZxz2!=null and lmdjcspjlZxz2!=''">
        and t.lmdjcspjl_zxz2=#{lmdjcspjlZxz2}
      </if>
      <if test="lmdjcspjlZhz3!=null and lmdjcspjlZhz3!=''">
        and t.lmdjcspjl_zhz3=#{lmdjcspjlZhz3}
      </if>
      <if test="lmdjcspjlZhz4!=null and lmdjcspjlZhz4!=''">
        and t.lmdjcspjl_zhz4=#{lmdjcspjlZhz4}
      </if>
      <if test="lmdjcczjlZhz1!=null and lmdjcczjlZhz1!=''">
        and t.lmdjcczjl_zhz1=#{lmdjcczjlZhz1}
      </if>
      <if test="lmdjcczjlZhz2!=null and lmdjcczjlZhz2!=''">
        and t.lmdjcczjl_zhz2=#{lmdjcczjlZhz2}
      </if>
      <if test="lmdjcczjlZxz2!=null and lmdjcczjlZxz2!=''">
        and t.lmdjcczjl_zxz2=#{lmdjcczjlZxz2}
      </if>
      <if test="lmdjcczjlZhz3!=null and lmdjcczjlZhz3!=''">
        and t.lmdjcczjl_zhz3=#{lmdjcczjlZhz3}
      </if>
      <if test="lmdjcczjlZhz4!=null and lmdjcczjlZhz4!=''">
        and t.lmdjcczjl_zhz4=#{lmdjcczjlZhz4}
      </if>
      <if test="kgyxdcljzt!=null and kgyxdcljzt!=''">
        and t.kgyxdcljzt=#{kgyxdcljzt}
      </if>
      <if test="jyjsffhbz!=null and jyjsffhbz!=''">
        and t.jyjsffhbz=#{jyjsffhbz}
      </if>
      <if test="xmzjcxcztyyggjcctg!=null and xmzjcxcztyyggjcctg!=''">
        and t.xmzjcxcztyyggjcctg=#{xmzjcxcztyyggjcctg}
      </if>
      <if test="operator!=null and operator!=''">
        and t.operator like concat('%', #{operator}, '%')
      </if>
      <if test="remark!=null and remark!=''">
        and t.remark=#{remark}
      </if>
      <if test="submitState!=null and submitState!=''">
        and t.submit_state <![CDATA[ >= ]]> #{submitState}
      </if>
      <if test="delFlag!=null and delFlag!=''">
        and t.del_flag=#{delFlag}
      </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>
    </where>
    <if test="orderBy!=null and orderBy!=''">
      order by t.${orderBy}
    </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="getJcebList" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List"/>
    FROM bus_jceb_gjsfx t
    LEFT JOIN v_station v on t.dept_station_id = v.dept_station_id
    <where>
      t.del_flag='0'
      <if test="submitState!=null and submitState!=''">
        and t.submit_state <![CDATA[ >= ]]> #{submitState}
      </if>
      <if test="deptId!=null and deptId!=''">
        and v.dept_id=#{deptId}
      </if>
      <if test="stationId!=null and stationId!=''">
        and v.station_id=#{stationId}
      </if>
      <if test="stationIds!=null and stationIds.length > 0">
        and v.station_id in
        <foreach collection="stationIds" item="stationId" index="i" open="(" separator="," close=")">
          #{stationId}
        </foreach>
      </if>
      <if test="pillarCode!=null and pillarCode!=''">
        and t.pillar_start=#{pillarCode}
      </if>
      <if test="operator!=null and operator!=''">
        and t.operator like concat('%', #{operator}, '%')
      </if>
      <if test="createBy!=null and createBy!=''">
        and t.create_by=#{createBy}
      </if>
    </where>
  </select>

  <resultMap id="JcebResultMap" type="com.railway.business.catenary.domain.vo.JcebRecordVo">
    <result column="id" property="id"/>
    <result column="dept_name" property="deptName"/>
    <result column="station_name" property="stationName"/>
    <result column="operation_date" property="operationDate"/>
    <result column="operator" property="operator"/>
    <result column="jcebType" property="jcebType"/>
  </resultMap>

  <select id="getListByType" resultMap="JcebResultMap">
    SELECT t.id, v.dept_name, v.station_name, t.operation_date, t.operator, #{jcebType} as jcebType
    FROM bus_jceb_gjsfx t
    LEFT JOIN v_station v on t.dept_station_id = v.dept_station_id
    <where>
      t.del_flag='0'
      <if test="submitState!=null and submitState!=''">
        and t.submit_state <![CDATA[ >= ]]> #{submitState}
      </if>
      <if test="deptId!=null and deptId!=''">
        and v.dept_id=#{deptId}
      </if>
      <if test="stationId!=null and stationId!=''">
        and v.station_id=#{stationId}
      </if>
      <if test="stationIds!=null and stationIds.length > 0">
        and v.station_id in
        <foreach collection="stationIds" item="stationId" index="i" open="(" separator="," close=")">
          #{stationId}
        </foreach>
      </if>
      <if test="pillarCode!=null and pillarCode!=''">
        and t.pillar_start=#{pillarCode}
      </if>
      <if test="operator!=null and operator!=''">
        and t.operator like concat('%', #{operator}, '%')
      </if>
      <if test="createBy!=null and createBy!=''">
        and t.create_by=#{createBy}
      </if>
    </where>
  </select>

  <resultMap id="JcebCountResultMap" type="com.railway.business.catenary.domain.vo.JcebCountVo">
    <result column="c" property="count"/>
    <result column="operation_date" property="operationDate"/>
  </resultMap>

  <select id="getCountByType" resultMap="JcebCountResultMap">
    SELECT count(*) c, max(t.operation_date) as operation_date
    FROM bus_jceb_gjsfx t
    LEFT JOIN v_station v on t.dept_station_id = v.dept_station_id
    <where>
      t.del_flag='0'
      <if test="submitState!=null and submitState!=''">
        and t.submit_state <![CDATA[ >= ]]> #{submitState}
      </if>
      <if test="deptId!=null and deptId!=''">
        and v.dept_id=#{deptId}
      </if>
      <if test="stationId!=null and stationId!=''">
        and v.station_id=#{stationId}
      </if>
      <if test="pillarCode!=null and pillarCode!=''">
        and t.pillar_start=#{pillarCode}
      </if>
      <if test="operator!=null and operator!=''">
        and t.operator like concat('%', #{operator}, '%')
      </if>
      <if test="createBy!=null and createBy!=''">
        and t.create_by=#{createBy}
      </if>
    </where>
  </select>

</mapper>