<?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.yunfeiyun.agmp.fms.mapper.TssTracecodeapplyMapper">

    <resultMap type="TssTracecodeapply" id="TssTracecodeapplyResult">
        <result property="tarcecodeapplyId" column="tarcecodeapplyId"/>
        <result property="tarcecodeapplyno" column="tarcecodeapplyno"/>
        <result property="orderId" column="orderId"/>
        <result property="tracecodetplId" column="tracecodetplId"/>
        <result property="stockId" column="stockId"/>
        <result property="tarcecodeapplyCreator" column="tarcecodeapplyCreator"/>
        <result property="tarcecodeapplyCreateddate" column="tarcecodeapplyCreateddate"/>
    </resultMap>

    <sql id="selectTssTracecodeapplyVo">
        select tarcecodeapplyId, tarcecodeapplyno, orderId, tracecodetplId, stockId, tarcecodeapplyCreator,
        tarcecodeapplyCreateddate from TssTracecodeapply
    </sql>

    <select id="selectTssTracecodeapplyList" parameterType="TssTracecodeapply" resultMap="TssTracecodeapplyResult">
        <include refid="selectTssTracecodeapplyVo"/>
        <where>
            <if test="tarcecodeapplyno != null  and tarcecodeapplyno != ''">and tarcecodeapplyno = #{tarcecodeapplyno}
            </if>
            <if test="tarcecodeapplyBatchno !=null and tarcecodeapplyBatchno !=''">and tarcecodeapplyBatchno =
                #{tarcecodeapplyBatchno}
            </if>
            <if test="orderId != null  and orderId != ''">and orderId = #{orderId}</if>
            <if test="tracecodetplId != null  and tracecodetplId != ''">and tracecodetplId = #{tracecodetplId}</if>
            <if test="stockId != null  and stockId != ''">and stockId = #{stockId}</if>
            <if test="tarcecodeapplyCreator != null  and tarcecodeapplyCreator != ''">and tarcecodeapplyCreator =
                #{tarcecodeapplyCreator}
            </if>
            <if test="tarcecodeapplyCreateddate != null  and tarcecodeapplyCreateddate != ''">and
                tarcecodeapplyCreateddate = #{tarcecodeapplyCreateddate}
            </if>
        </where>
    </select>

    <select id="selectTssTracecodeapplyListByTarcecodeapplynos" parameterType="java.util.Collection" resultMap="TssTracecodeapplyResult">
        <include refid="selectTssTracecodeapplyVo"/>
        where tarcecodeapplyno in
        <foreach item="tarcecodeapplyno" collection="array" open="(" separator="," close=")">
            #{tarcecodeapplyno}
        </foreach>
    </select>

    <select id="selectTssTracecodeapplyByTarcecodeapplyId" parameterType="String" resultMap="TssTracecodeapplyResult">
        <include refid="selectTssTracecodeapplyVo"/>
        where tarcecodeapplyId = #{tarcecodeapplyId}
    </select>

    <select id="selectCountOfTssTracecodeapply" parameterType="SelectCountOfScreenReqVo" resultType="Integer">
        select count(1) from TssTracecodeapply
        <where>
            <if test="orderId != null  and orderId != ''">and orderId = #{orderId}</if>
            <if test="orderStatus != null  and orderStatus == '1'">and orderId is not null</if>
            <if test="startTime != null  and startTime != ''">and tarcecodeapplyCreateddate >= #{startTime}</if>
            <if test="endTime != null  and endTime != ''">and tarcecodeapplyCreateddate <![CDATA[ <= ]]> #{endTime}</if>
        </where>
    </select>

    <insert id="insertTssTracecodeapply" parameterType="TssTracecodeapply">
        insert into TssTracecodeapply
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="tarcecodeapplyId != null">tarcecodeapplyId,</if>
            <if test="tarcecodeapplyno != null">tarcecodeapplyno,</if>
            <if test="orderId != null">orderId,</if>
            <if test="tracecodetplId != null">tracecodetplId,</if>
            <if test="stockId != null">stockId,</if>
            <if test="tarcecodeapplyCreator != null">tarcecodeapplyCreator,</if>
            <if test="tarcecodeapplyCreateddate != null">tarcecodeapplyCreateddate,</if>
            <if test="tarcecodeapplyBatchno != null">tarcecodeapplyBatchno,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="tarcecodeapplyId != null">#{tarcecodeapplyId},</if>
            <if test="tarcecodeapplyno != null">#{tarcecodeapplyno},</if>
            <if test="orderId != null">#{orderId},</if>
            <if test="tracecodetplId != null">#{tracecodetplId},</if>
            <if test="stockId != null">#{stockId},</if>
            <if test="tarcecodeapplyCreator != null">#{tarcecodeapplyCreator},</if>
            <if test="tarcecodeapplyCreateddate != null">#{tarcecodeapplyCreateddate},</if>
            <if test="tarcecodeapplyBatchno != null">#{tarcecodeapplyBatchno},</if>
        </trim>
    </insert>

    <insert id="insertTssTracecodeapplyByBatch" parameterType="TssTracecodeapply">
        INSERT INTO TssTracecodeapply
        (
        tarcecodeapplyId,
        tarcecodeapplyBatchno,
        tarcecodeapplyno,
        orderId,
        tracecodetplId,
        stockId,
        tarcecodeapplyCreator,
        tarcecodeapplyCreateddate
        )
        VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.tarcecodeapplyId},
            #{item.tarcecodeapplyBatchno},
            #{item.tarcecodeapplyno},
            #{item.orderId},
            #{item.tracecodetplId},
            #{item.stockId},
            #{item.tarcecodeapplyCreator},
            #{item.tarcecodeapplyCreateddate}
            )
        </foreach>
    </insert>

    <update id="updateTssTracecodeapply" parameterType="TssTracecodeapply">
        update TssTracecodeapply
        <trim prefix="SET" suffixOverrides=",">
            <if test="tarcecodeapplyno != null">tarcecodeapplyno = #{tarcecodeapplyno},</if>
            <if test="orderId != null">orderId = #{orderId},</if>
            <if test="tracecodetplId != null">tracecodetplId = #{tracecodetplId},</if>
            <if test="stockId != null">stockId = #{stockId},</if>
            <if test="tarcecodeapplyCreator != null">tarcecodeapplyCreator = #{tarcecodeapplyCreator},</if>
            <if test="tarcecodeapplyCreateddate != null">tarcecodeapplyCreateddate = #{tarcecodeapplyCreateddate},</if>
        </trim>
        where tarcecodeapplyId = #{tarcecodeapplyId}
    </update>

    <update id="matchSpmOrder" parameterType="TssTracecodeapply">
        update TssTracecodeapply
        set orderId = #{spmOrderId}
        where tarcecodeapplyno = #{tarcecodeapplyno}
    </update>

    <update id="unMatchSpmOrder" parameterType="String">
        update TssTracecodeapply
        set orderId = null
        where tarcecodeapplyno = #{tarcecodeapplyno}
    </update>

    <delete id="deleteTssTracecodeapplyByTarcecodeapplyId" parameterType="String">
        delete from TssTracecodeapply where tarcecodeapplyId = #{tarcecodeapplyId}
    </delete>

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

    <select id="selectTssTracecodeapplyManagerListResVoList" parameterType="com.yunfeiyun.agmp.fms.domain.reqvo.TssTracecodeapplyReqVo" resultType="com.yunfeiyun.agmp.fms.domain.resvo.TssTracecodeapplyManagerListResVo">
        SELECT
        tca.tarcecodeapplyBatchno,
        tca.tracecodetplId,
        COUNT( tca.orderId ) as useNumber,
        COUNT( tca.tarcecodeapplyBatchno ) as totalNumber,
        wfg.finishgoodscode,
        wfg.finishgoodNo,
        tca.tarcecodeapplyCreateddate,
        CONCAT(MIN(tca.tarcecodeapplyno), '-', MAX(tca.tarcecodeapplyno)) as traceCodeApplyConcat
        FROM
        TssTracecodeapply tca
        LEFT JOIN WmsStock ws ON ws.stockId = tca.stockId
        LEFT JOIN WmsFinishgoods wfg on wfg.goodsId = ws.goodsId
        LEFT JOIN WmsGoods wg on wg.goodsId = wfg.goodsId
        <where>
            <if test="tarcecodeapplyno != null  and tarcecodeapplyno != ''">and tca.tarcecodeapplyno = #{tarcecodeapplyno}
            </if>
            <if test="tarcecodeapplyBatchno !=null and tarcecodeapplyBatchno !=''">and tca.tarcecodeapplyBatchno =
                #{tarcecodeapplyBatchno}
            </if>
            <if test="orderId != null  and orderId != ''">and tca.orderId = #{orderId}</if>
            <if test="tracecodetplId != null  and tracecodetplId != ''">and tca.tracecodetplId = #{tracecodetplId}</if>
            <if test="stockId != null  and stockId != ''">and tca.stockId = #{stockId}</if>
            <if test="tarcecodeapplyCreator != null  and tarcecodeapplyCreator != ''">and tca.tarcecodeapplyCreator =
                #{tarcecodeapplyCreator}
            </if>
            <if test="tarcecodeapplyCreateddate != null  and tarcecodeapplyCreateddate != ''">and
                tca.tarcecodeapplyCreateddate = #{tarcecodeapplyCreateddate}
            </if>
            <if test="goodsName != null and goodsName != ''">and
                (wg.goodsName like CONCAT("%",#{goodsName},'%')
                or wfg.finishgoodPickno like concat('%', #{goodsName}, '%'))
            </if>
        </where>
        GROUP BY
        tarcecodeapplyBatchno,wfg.finishgoodscode,wfg.finishgoodNo,tca.tarcecodeapplyCreateddate,	tca.tracecodetplId
    </select>
    <select id="selectTssTraceCodeApplyResVoList" parameterType="com.yunfeiyun.agmp.fms.domain.TssTracecodeapply" resultType="com.yunfeiyun.agmp.fms.domain.resvo.TssTraceCodeApplyResVo">
        SELECT
        tca.*,
        wfg.finishgoodscode,
        wfg.finishgoodNo
        FROM
        TssTracecodeapply tca
        LEFT JOIN WmsStock ws ON ws.stockId = tca.stockId
        LEFT JOIN WmsFinishgoods wfg on wfg.goodsId = ws.goodsId
        <where>
            <if test="tarcecodeapplyno != null  and tarcecodeapplyno != ''">and tca.tarcecodeapplyno = #{tarcecodeapplyno}
            </if>
            <if test="tarcecodeapplyBatchno !=null and tarcecodeapplyBatchno !=''">and tca.tarcecodeapplyBatchno =
                #{tarcecodeapplyBatchno}
            </if>
            <if test="orderId != null  and orderId != ''">and tca.orderId = #{orderId}</if>
            <if test="tracecodetplId != null  and tracecodetplId != ''">and tca.tracecodetplId = #{tracecodetplId}</if>
            <if test="stockId != null  and stockId != ''">and tca.stockId = #{stockId}</if>
            <if test="tarcecodeapplyCreator != null  and tarcecodeapplyCreator != ''">and tca.tarcecodeapplyCreator =
                #{tarcecodeapplyCreator}
            </if>
            <if test="tarcecodeapplyCreateddate != null  and tarcecodeapplyCreateddate != ''">and
                tca.tarcecodeapplyCreateddate = #{tarcecodeapplyCreateddate}
            </if>
        </where>
    </select>
</mapper>