<?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.ruoyi.shipping.mapper.TCntrtrackingMapper">
    
    <resultMap type="TCntrtracking" id="TCntrtrackingResult">
        <result property="fId"    column="f_id"    />
        <result property="fNo"    column="f_no"    />
        <result property="fMblno"    column="f_mblno"    />
        <result property="fTypeid"    column="f_typeid"    />
        <result property="fOwner"    column="f_owner"    />
        <result property="fVsl"    column="f_vsl"    />
        <result property="fVoy"    column="f_voy"    />
        <result property="fUpdatetime"    column="f_updatetime"    />
        <result property="fUpdateaddress"    column="f_updateaddress"    />
        <result property="fUpdateef"    column="f_updateEF"    />
        <result property="fCntrstatus"    column="f_cntrstatus"    />
        <result property="fStatus"    column="f_status"    />
        <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="remark"    column="remark"    />
        <result property="fOpctnstatus" column="f_opctnstatus"/>
    </resultMap>

    <sql id="selectTCntrtrackingVo">
        select f_id, f_no, f_mblno, f_typeid, f_owner, f_vsl, f_voy, f_updatetime, f_updateaddress, f_updateEF, f_cntrstatus, case when f_status = 'T' then '正常' else '停用' end as f_status,f_opctnstatus, create_by, create_time, update_by, update_time, remark from t_cntrtracking
    </sql>

    <select id="selectTCntrtrackingList" parameterType="TCntrtracking" resultMap="TCntrtrackingResult">
        SELECT
        tc.f_id,
        tc.f_no,
        tc.f_typeid,
        tc.f_mblno,
        t.f_no typeidName,
        t.f_cntrsize,
        prs.dict_label cntrsizeName,
        tc.f_owner,
        pro.dict_label ownerName,
        tc.f_vsl,
        tc.f_voy,
        tc.f_updatetime,
        tc.f_updateaddress,
        address.f_name addressName,
        tc.f_updateEF,
        sdda.dict_label updateEFName,
        tc.f_cntrstatus,
        pr.dict_label cntrstatusName,
        case when tc.f_status = 'T' then '正常' else '停用' end as f_status,
        tc.f_opctnstatus,
        tc.create_by,
        tc.create_time,
        tc.update_by,
        tc.update_time,
        tc.remark
        FROM
        t_cntrtracking tc
        LEFT JOIN sys_dict_data pro ON pro.dict_value = tc.f_owner
        AND pro.dict_type = 'f_owner'
        LEFT JOIN sys_dict_data sdda ON sdda.dict_value = tc.f_updateEF
        AND sdda.dict_type = 'f_updateEF'
        LEFT JOIN sys_dict_data pr ON pr.dict_value = tc.f_cntrstatus
        AND pr.dict_type = 'f_cntrstatus'
        LEFT JOIN t_cntr t ON tc.f_typeid = t.f_id
        LEFT JOIN sys_dict_data prs ON prs.dict_value = t.f_cntrsize
        AND prs.dict_type = 'f_cntrsize'
        LEFT JOIN t_address address ON address.f_id = tc.f_updateaddress
        <where>  
            <if test="fNo != null  and fNo != ''"> and tc.f_no = #{fNo}</if>
            <if test="fMblno != null  and fMblno != ''"> and tc.f_mblno = #{fMblno}</if>
            <if test="fTypeid != null "> and tc.f_typeid = #{fTypeid}</if>
            <if test="fOwner != null  and fOwner != ''"> and tc.f_owner = #{fOwner}</if>
            <if test="fVsl != null  and fVsl != ''"> and tc.f_vsl = #{fVsl}</if>
            <if test="fVoy != null  and fVoy != ''"> and tc.f_voy = #{fVoy}</if>
            <if test="fUpdatetime != null "> and tc.f_updatetime = #{fUpdatetime}</if>
            <if test="fUpdateaddress != null  and fUpdateaddress != ''"> and tc.f_updateaddress = #{fUpdateaddress}</if>
            <if test="fUpdateef != null  and fUpdateef != ''"> and tc.f_updateEF = #{fUpdateef}</if>
            <if test="fCntrstatus != null  and fCntrstatus != ''"> and tc.f_cntrstatus = #{fCntrstatus}</if>
            <if test="fStatus != null  and fStatus != ''"> and tc.f_status = #{fStatus}</if>
            <if test="fOpctnstatus != null  and fOpctnstatus != ''">and f_opctnstatus = #{fOpctnstatus}</if>
            <if test='cLoadDate != null and cLoadDate[0] != null and cLoadDate[0]!= ""'>
                and tc.f_updatetime &gt;= #{cLoadDate[0]}
            </if>
            <if test='cLoadDate != null and cLoadDate[1] != null and cLoadDate[1]!= ""'>
                and tc.f_updatetime &lt;= #{cLoadDate[1]}
            </if>
        </where>
        ORDER BY tc.f_updatetime desc,tc.f_no
    </select>
    
    <select id="selectTCntrtrackingById" parameterType="Long" resultMap="TCntrtrackingResult">
        <include refid="selectTCntrtrackingVo"/>
        where f_id = #{fId}
    </select>
        
    <insert id="insertTCntrtracking" parameterType="TCntrtracking">
        <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="fId">
            SELECT LAST_INSERT_ID() AS fId
        </selectKey>
        insert into t_cntrtracking
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="fId != null">f_id,</if>
            <if test="fNo != null">f_no,</if>
            <if test="fMblno != null">f_mblno,</if>
            <if test="fTypeid != null">f_typeid,</if>
            <if test="fOwner != null">f_owner,</if>
            <if test="fVsl != null">f_vsl,</if>
            <if test="fVoy != null">f_voy,</if>
            <if test="fUpdatetime != null">f_updatetime,</if>
            <if test="fUpdateaddress != null">f_updateaddress,</if>
            <if test="fUpdateef != null">f_updateEF,</if>
            <if test="fCntrstatus != null and fCntrstatus != ''">f_cntrstatus,</if>
            <if test="fOpctnstatus != null  and fOpctnstatus != ''">f_opctnstatus,</if>
            <if test="fStatus != null">f_status,</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="remark != null">remark,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="fId != null">#{fId},</if>
            <if test="fNo != null">#{fNo},</if>
            <if test="fMblno != null">#{fMblno},</if>
            <if test="fTypeid != null">#{fTypeid},</if>
            <if test="fOwner != null">#{fOwner},</if>
            <if test="fVsl != null">#{fVsl},</if>
            <if test="fVoy != null">#{fVoy},</if>
            <if test="fUpdatetime != null">#{fUpdatetime},</if>
            <if test="fUpdateaddress != null">#{fUpdateaddress},</if>
            <if test="fUpdateef != null">#{fUpdateef},</if>
            <if test="fCntrstatus != null and fCntrstatus != ''">#{fCntrstatus},</if>
            <if test="fOpctnstatus != null  and fOpctnstatus != ''">#{fOpctnstatus},</if>
            <if test="fStatus != null">#{fStatus},</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="remark != null">#{remark},</if>
         </trim>
    </insert>

    <update id="updateTCntrtracking" parameterType="TCntrtracking">
        update t_cntrtracking
        <trim prefix="SET" suffixOverrides=",">
            <if test="fNo != null">f_no = #{fNo},</if>
            <if test="fMblno != null">f_mblno = #{fMblno},</if>
            <if test="fTypeid != null">f_typeid = #{fTypeid},</if>
            <if test="fOwner != null">f_owner = #{fOwner},</if>
            <if test="fVsl != null">f_vsl = #{fVsl},</if>
            <if test="fVoy != null">f_voy = #{fVoy},</if>
            <if test="fUpdatetime != null">f_updatetime = #{fUpdatetime},</if>
            <if test="fUpdateaddress != null">f_updateaddress = #{fUpdateaddress},</if>
            <if test="fUpdateef != null">f_updateEF = #{fUpdateef},</if>
            <if test="fCntrstatus != null and fCntrstatus != ''">f_cntrstatus = #{fCntrstatus},</if>
            <if test="fStatus != null">f_status = #{fStatus},</if>
            <if test="fOpctnstatus != null  and fOpctnstatus != ''">f_opctnstatus = #{fOpctnstatus},</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="remark != null">remark = #{remark},</if>
        </trim>
        where f_id = #{fId}
    </update>

    <delete id="deleteTCntrtrackingById" parameterType="Long">
        delete from t_cntrtracking where f_id = #{fId}
    </delete>

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