<?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.TDkFieldworkMapper">
    
    <resultMap type="TDkFieldwork" id="TDkFieldworkResult">
        <result property="id"    column="id"    />
        <result property="dkjdxxId"    column="dkjdxx_id"    />
        <result property="stepState"    column="step_state"    />
        <result property="endTime"    column="end_time"    />
        <result property="fieldworkId"    column="fieldwork_id"    />
        <result property="fieldworkBy"    column="fieldwork_by"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="notes"    column="notes"    />
        <result property="dkbh"    column="dkbh"    />
        <result property="dkmj"    column="dkmj"    />
        <result property="geom"    column="geom"    />
        <result property="confirmState"    column="confirm_state"    />
        <result property="confirmType"    column="confirm_type"    />
        <result property="confirmNotse"    column="confirm_notse"    />
        <result property="confirmTime"    column="confirm_time"    />
        <result property="auditflow"    column="auditflow"    />
        <result property="lzb"    column="lzb"    />
        <result property="bzb"    column="bzb"    />
    </resultMap>

    <sql id="selectTDkFieldworkVo">
        select id, dkjdxx_id, step_state, end_time, fieldwork_id, fieldwork_by,
               create_by, create_time, notes, dkbh, dkmj, public.st_asewkt(geom) geom,
               public.ST_Centroid(geom) centroid,
               confirm_state, confirm_type, confirm_notse, confirm_time,auditflow from t_dk_fieldwork
    </sql>

    <select id="selectTDkFieldworkList" parameterType="TDkFieldwork" resultMap="TDkFieldworkResult">
        select t1.*,public.ST_X(centroid) lzb,public.ST_Y(centroid) bzb from (
        <include refid="selectTDkFieldworkVo"/>
        <where>
            <if test="dkbh != null  and dkbh != ''"> and dkbh = #{dkbh}</if>
            <if test="dkjdxxId != null  and dkjdxxId != ''"> and dkjdxx_id = #{dkjdxxId}</if>
            <if test="stepState != null  and stepState != ''"> and step_state = #{stepState}</if>
            <if test="stepStateS != null  and stepStateS.length > 0">
                and step_state in (
                <foreach collection="stepStateS" item="item"  separator=",">
                    #{item}
                </foreach>
                )
            </if>
            <if test="endTime != null  and endTime != ''"> and end_time = #{endTime}</if>
            <if test="fieldworkId != null  and fieldworkId != ''"> and fieldwork_id = #{fieldworkId}</if>
            <if test="fieldworkBy != null  and fieldworkBy != ''"> and fieldwork_by = #{fieldworkBy}</if>
            <if test="notes != null  and notes != ''"> and notes = #{notes}</if>
        </where>)t1
        order by create_time desc
    </select>
    
    <select id="selectTDkFieldworkById" parameterType="String" resultMap="TDkFieldworkResult">
        <include refid="selectTDkFieldworkVo"/>
        where id = #{id}
    </select>

    <select id="hcCount" parameterType="String" resultType="map">
        select coalesce(sum(hz),0) hz,coalesce(sum(dhc),0) dhc,coalesce(sum(hcz),0) hcz,
        coalesce(sum(yhc),0) yhc,coalesce(sum(th),0) th from (
        select 1 as hz,
            case when step_state='0' then 1 else 0 end as dhc,
            case when step_state='1' or step_state='2' then 1 else 0 end as hcz,
            case when step_state='3' then 1 else 0 end as yhc,
            case when step_state='-1' then 1 else 0 end as th
        from t_dk_fieldwork
        where fieldwork_id = #{userId} )t1
    </select>

    <insert id="insertTDkFieldwork" parameterType="TDkFieldwork">
        insert into t_dk_fieldwork
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">id,</if>
            <if test="dkjdxxId != null and dkjdxxId != ''">dkjdxx_id,</if>
            <if test="stepState != null">step_state,</if>
            <if test="endTime != null">end_time,</if>
            <if test="fieldworkId != null">fieldwork_id,</if>
            <if test="fieldworkBy != null">fieldwork_by,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="notes != null">notes,</if>
            <if test="dkbh != null and dkbh != ''">dkbh,</if>
            <if test="dkmj != null">dkmj,</if>
            <if test="geom != null">geom,</if>
            <if test="confirmState != null">confirm_state,</if>
            <if test="confirmType != null">confirm_type,</if>
            <if test="confirmNotse != null">confirm_notse,</if>
            <if test="confirmTime != null">confirm_time,</if>
            <if test="auditflow != null and auditflow != ''">auditflow,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">#{id},</if>
            <if test="dkjdxxId != null and dkjdxxId != ''">#{dkjdxxId},</if>
            <if test="stepState != null">#{stepState},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="fieldworkId != null">#{fieldworkId},</if>
            <if test="fieldworkBy != null">#{fieldworkBy},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="notes != null">#{notes},</if>
            <if test="dkbh != null and dkbh != ''">#{dkbh},</if>
            <if test="dkmj != null">#{dkmj},</if>
            <if test="geom != null">public.st_geomfromewkt(#{geom}),</if>
            <if test="confirmState != null">#{confirmState},</if>
            <if test="confirmType != null">#{confirmType},</if>
            <if test="confirmNotse != null">#{confirmNotse},</if>
            <if test="confirmTime != null">#{confirmTime},</if>
            <if test="auditflow != null and auditflow != ''">#{auditflow},</if>
         </trim>
    </insert>

    <update id="updateTDkFieldwork" parameterType="TDkFieldwork">
        update t_dk_fieldwork
        <trim prefix="SET" suffixOverrides=",">
            <if test="dkjdxxId != null and dkjdxxId != ''">dkjdxx_id = #{dkjdxxId},</if>
            <if test="stepState != null">step_state = #{stepState},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="fieldworkId != null">fieldwork_id = #{fieldworkId},</if>
            <if test="fieldworkBy != null">fieldwork_by = #{fieldworkBy},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="notes != null">notes = #{notes},</if>
            <if test="dkbh != null and dkbh != ''">dkbh = #{dkbh},</if>
            <if test="dkmj != null">dkmj = #{dkmj},</if>
            <if test="geom != null">geom = public.st_geomfromewkt(#{geom}),</if>
            <if test="confirmState != null">confirm_state = #{confirmState},</if>
            <if test="confirmType != null">confirm_type = #{confirmType},</if>
            <if test="confirmNotse != null">confirm_notse = #{confirmNotse},</if>
            <if test="confirmTime != null">confirm_time = #{confirmTime},</if>
            <if test="auditflow != null and auditflow != ''">auditflow = #{auditflow},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteTDkFieldworkById" parameterType="String">
        delete from t_dk_fieldwork where id = #{id}
    </delete>

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