<?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.siwei.rsmonitoring.mapper.TDkjbxxMapper">

    <resultMap type="TDkjbxx" id="TDkjbxxResult">
        <result property="id"    column="id"    />
        <result property="dkbh"    column="dkbh"    />
        <result property="dkmj"    column="dkmj"    />
        <result property="xzqdm"    column="xzqdm"    />
        <result property="xzqmc"    column="xzqmc"    />
        <result property="qlx"    column="qlx"    />
        <result property="hlx"    column="hlx"    />
        <result property="qsx"    column="qsx"    />
        <result property="hsx"    column="hsx"    />
        <result property="lzb"    column="lzb"    />
        <result property="bzb"    column="bzb"    />
        <result property="shpJson"    column="shp_json"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="auditflow"    column="auditflow"    />
        <result property="pcsjid"    column="pcsjid"    />
        <result property="geom"    column="geom"    />
        <result property="qsxtif"    column="qsxtif"    />
        <result property="hsxtif"    column="hsxtif"    />
        <result property="splitType"    column="split_type"    />
        <result property="fieldworkId"    column="fieldwork_id"    />
    </resultMap>

    <resultMap type="DataCountVo" id="DataCountVoResult">
        <result property="countNumber"    column="count_number"    />
        <result property="countSum"    column="count_sum"    />
        <result property="auditflow"    column="auditflow_type"    />
        <result property="auditflowCode"    column="auditflow_step"    />
        <result property="endTime"    column="endTime"    />
        <result property="startTime"    column="startTime"    />
    </resultMap>

    <sql id="selectTDkjbxxVo">
        select id, dkbh, dkmj, xzqdm, xzqmc, qlx, hlx, qsx, hsx, lzb, bzb, shp_json, create_by, create_time, update_by, update_time, auditflow, pcsjid,
        public.st_asewkt(public.st_transform(geom, #{shpOutSRID})) geom,
        qsxtif, hsxtif from t_dkjbxx
    </sql>

    <select id="selectTDkjbxxList" parameterType="TDkjbxx" resultMap="TDkjbxxResult">
        select t1.* from (
        <include refid="selectTDkjbxxVo"/>
        <where>
            <if test="dkbh != null  and dkbh != ''">and dkbh like concat('%', #{dkbh}, '%')</if>
            <if test="dkmj != null  and dkmj != ''"> and dkmj::NUMERIC ${dkmjbs} #{dkmj}::NUMERIC</if>
            <if test="xzqdm != null  and xzqdm != ''"> and xzqdm = concat('%', #{xzqdm}, '%')</if>
            <if test="xzqdms != null  and !xzqdms.isEmpty()">
             and xzqdm in (
                <foreach collection="xzqdms" item="xzq"  separator=",">
                    #{xzq}
                </foreach>
            )
            </if>
            <if test="xzqmc != null  and xzqmc != ''"> and xzqmc = #{xzqmc}</if>
            <if test="qlx != null  and qlx != ''"> and qlx = #{qlx}</if>
            <if test="hlx != null  and hlx != ''"> and hlx = #{hlx}</if>
            <if test="qsx != null  and qsx != ''"> and qsx = #{qsx}</if>
            <if test="hsx != null  and hsx != ''"> and hsx = #{hsx}</if>
            <if test="lzb != null  and lzb != ''"> and lzb = #{lzb}</if>
            <if test="bzb != null  and bzb != ''"> and bzb = #{bzb}</if>
            <if test="shpJson != null  and shpJson != ''"> and shp_json = #{shpJson}</if>
            <if test="auditflow != null  and auditflow != ''"> and auditflow = #{auditflow}</if>
            <if test="pcsjid != null  and pcsjid != ''"> and pcsjid = #{pcsjid}</if>
            <if test="qsxtif != null  and qsxtif != ''"> and qsxtif = #{qsxtif}</if>
            <if test="hsxtif != null  and hsxtif != ''"> and hsxtif = #{hsxtif}</if>
            <if test="splitType != null "> and split_type = #{splitType}</if>
            <if test="startTime != null  and startTime != ''"> and hsx &gt;= #{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and hsx &lt;= #{endTime}</if>
        </where>
            )t1 left join t_dkjbxx_state t2 on t1.id = t2.id
        <where>
            <if test="auditflowList != null and !auditflowList.isEmpty() ">
                 (
                <foreach collection="auditflowList" item="audit"  separator="or">
                    (
                    <if test="audit.auditflowStep != null  and audit.auditflowStep != ''">t2.step_code = #{audit.auditflowStep} </if>
                    <if test="audit.successType != null  and audit.successType != ''"> and t2.success_type = #{audit.successType}</if>
                    )
                </foreach>
                )
            </if>
        </where>
        <if test="descValue != null  and descValue != ''"> order by t1.${descValue} desc </if>
    </select>

    <select id="selectTDkjbxxById"  resultMap="TDkjbxxResult">
        <include refid="selectTDkjbxxVo"/>
        where id = #{id}
    </select>

    <select id="selectTDkjbxxByDkbh"  resultMap="TDkjbxxResult">
        <include refid="selectTDkjbxxVo"/>
        where dkbh = #{dkbh}
    </select>


    <insert id="insertTDkjbxx" parameterType="TDkjbxx">
        insert into t_dkjbxx
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">id,</if>
            <if test="dkbh != null and dkbh != ''">dkbh,</if>
            <if test="dkmj != null">dkmj,</if>
            <if test="xzqdm != null">xzqdm,</if>
            <if test="xzqmc != null">xzqmc,</if>
            <if test="qlx != null">qlx,</if>
            <if test="hlx != null">hlx,</if>
            <if test="qsx != null">qsx,</if>
            <if test="hsx != null">hsx,</if>
            <if test="lzb != null">lzb,</if>
            <if test="bzb != null">bzb,</if>
            <if test="shpJson != null">shp_json,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="auditflow != null and auditflow != ''">auditflow,</if>
            <if test="pcsjid != null">pcsjid,</if>
            <if test="geom != null">geom,</if>
            <if test="qsxtif != null">qsxtif,</if>
            <if test="hsxtif != null">hsxtif,</if>
            <if test="splitType != null ">split_type,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">#{id},</if>
            <if test="dkbh != null and dkbh != ''">#{dkbh},</if>
            <if test="dkmj != null">#{dkmj},</if>
            <if test="xzqdm != null">#{xzqdm},</if>
            <if test="xzqmc != null">#{xzqmc},</if>
            <if test="qlx != null">#{qlx},</if>
            <if test="hlx != null">#{hlx},</if>
            <if test="qsx != null">#{qsx},</if>
            <if test="hsx != null">#{hsx},</if>
            <if test="lzb != null">#{lzb},</if>
            <if test="bzb != null">#{bzb},</if>
            <if test="shpJson != null">#{shpJson},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="auditflow != null and auditflow != ''">#{auditflow},</if>
            <if test="pcsjid != null">#{pcsjid},</if>
            <if test="geom != null">public.st_geomfromewkt(#{geom}),</if>
            <if test="qsxtif != null">#{qsxtif},</if>
            <if test="hsxtif != null">#{hsxtif},</if>
            <if test="splitType != null ">#{splitType},</if>
         </trim>
    </insert>

    <update id="updateTDkjbxx" parameterType="TDkjbxx">
        update t_dkjbxx
        <trim prefix="SET" suffixOverrides=",">
            <if test="dkbh != null and dkbh != ''">dkbh = #{dkbh},</if>
            <if test="dkmj != null">dkmj = #{dkmj},</if>
            <if test="xzqdm != null">xzqdm = #{xzqdm},</if>
            <if test="xzqmc != null">xzqmc = #{xzqmc},</if>
            <if test="qlx != null">qlx = #{qlx},</if>
            <if test="hlx != null">hlx = #{hlx},</if>
            <if test="qsx != null">qsx = #{qsx},</if>
            <if test="hsx != null">hsx = #{hsx},</if>
            <if test="lzb != null">lzb = #{lzb},</if>
            <if test="bzb != null">bzb = #{bzb},</if>
            <if test="shpJson != null">shp_json = #{shpJson},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="auditflow != null and auditflow != ''">auditflow = #{auditflow},</if>
            <if test="pcsjid != null">pcsjid = #{pcsjid},</if>
            <if test="geom != null">geom = public.st_geomfromewkt(#{geom}),</if>
            <if test="qsxtif != null">qsxtif = #{qsxtif},</if>
            <if test="hsxtif != null">hsxtif = #{hsxtif},</if>
            <if test="splitType != null and splitType != '' ">split_type = #{splitType},</if>
            <if test="fieldworkId != null and fieldworkId != '' ">fieldwork_id = #{fieldworkId},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="updateTDkjbxxByHgx" parameterType="TDkjbxx">
        update t_dkjbxx set
        split_type = #{splitType},fieldwork_id = #{fieldworkId},update_time = #{updateTime}
        <if test="updateBy != null">,update_by = #{updateBy}</if>
        where id = #{id}
    </update>

    <delete id="deleteTDkjbxxById" parameterType="String">
        delete from t_dkjbxx where id = #{id}
    </delete>

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

    <select id="selectTDkjbxxCctbCount"  resultMap="DataCountVoResult">
        select count(*) count_number,coalesce(sum(dkmj::numeric), 0) count_sum from (
        select t1.* from (
        select  * from t_dkjbxx
        <where>
        <if test="dataCountVo.xzqdms != null  and !dataCountVo.xzqdms.isEmpty()">
            and xzqdm in (
            <foreach collection="dataCountVo.xzqdms" item="xzq"  separator=",">
                #{xzq}
            </foreach>
            )
        </if>
        </where>
        )t1 left join t_dkjbxx_state t2 on t1.id = t2.id
        <where>
            <if test="dataCountVo.startTime != null  and dataCountVo.startTime != ''"> and t1.hsx &gt;= #{dataCountVo.startTime}</if>
            <if test="dataCountVo.endTime != null  and dataCountVo.endTime != ''"> and t1.hsx &lt;= #{dataCountVo.endTime}</if>
            <if test="dataCountVo.auditflowList != null">
                AND (
                <foreach collection="dataCountVo.auditflowList" item="audit"  separator="or">
                    (
                    <if test="audit.auditflowStep != null  and audit.auditflowStep != ''">t2.step_code = #{audit.auditflowStep} </if>
                    <if test="audit.successType != null  and audit.successType != ''"> and t2.success_type = #{audit.successType}</if>
                    )
                </foreach>
                )
            </if>
        </where>
        )t2
    </select>


</mapper>