<?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.TssScanrcdMapper">
    
    <resultMap type="TssScanrcd" id="TssScanrcdResult">
        <result property="scanrcdId"    column="scanrcdId"    />
        <result property="orderId"    column="orderId"    />
        <result property="tarcecodeapplyId"    column="tarcecodeapplyId"    />
        <result property="stockId"    column="stockId"    />
        <result property="scanrcdAddress"    column="scanrcdAddress"    />
        <result property="scanrcdCreateddate"    column="scanrcdCreateddate"    />
    </resultMap>

    <sql id="selectTssScanrcdVo">
        select scanrcdId, orderId, tarcecodeapplyId, stockId, scanrcdAddress, scanrcdCreateddate from TssScanrcd
    </sql>

    <select id="selectTssScanrcdList" parameterType="TssScanrcd" resultMap="TssScanrcdResult">
        <include refid="selectTssScanrcdVo"/>
        <where>  
            <if test="orderId != null  and orderId != ''"> and orderId = #{orderId}</if>
            <if test="tarcecodeapplyId != null  and tarcecodeapplyId != ''"> and tarcecodeapplyId = #{tarcecodeapplyId}</if>
            <if test="stockId != null  and stockId != ''"> and stockId = #{stockId}</if>
            <if test="scanrcdAddress != null  and scanrcdAddress != ''"> and scanrcdAddress = #{scanrcdAddress}</if>
            <if test="scanrcdCreateddate != null  and scanrcdCreateddate != ''"> and scanrcdCreateddate = #{scanrcdCreateddate}</if>
        </where>
    </select>
    
    <select id="selectTssScanrcdByScanrcdId" parameterType="String" resultMap="TssScanrcdResult">
        <include refid="selectTssScanrcdVo"/>
        where scanrcdId = #{scanrcdId}
    </select>
    <select id="selectTssScanrcdResVo" parameterType="com.yunfeiyun.agmp.fms.domain.reqvo.TssScanrcdReqVo" resultType="com.yunfeiyun.agmp.fms.domain.resvo.TssScanrcdResVo">
        SELECT
        ts.*,
        wfg.finishgoodNo,
        so.orderNo,
        tca.tarcecodeapplyno,
        d.dictName as finishGoodsName
        FROM
        TssScanrcd ts
        LEFT JOIN WmsStock ws ON ts.stockId = ws.stockId
        LEFT JOIN WmsFinishgoods wfg on wfg.goodsId = ws.goodsId
        LEFT JOIn SpmOrder so on so.orderId = ts.orderId
        LEFT JOIN TssTracecodeapply tca on tca.tarcecodeapplyId = ts.tarcecodeapplyId
        LEFT JOIN (select * from SysDict where dicttypeCode = 'agm_mpp_product') d on d.dictValue = wfg.finishgoodscode
        <where>
            <if test="orderId != null  and orderId != ''"> and ts.orderId = #{orderId}</if>
            <if test="tarcecodeapplyId != null  and tarcecodeapplyId != ''"> and ts.tarcecodeapplyId = #{tarcecodeapplyId}</if>
            <if test="stockId != null  and stockId != ''"> and ts.stockId = #{stockId}</if>
            <if test="scanrcdAddress != null  and scanrcdAddress != ''"> and ts.scanrcdAddress = #{scanrcdAddress}</if>
            <if test="scanrcdCreateddate != null  and scanrcdCreateddate != '' "> and ts.scanrcdCreateddate = #{scanrcdCreateddate}</if>
            <if test="scanTimeStart !=null and scanTimeStart != '' and scanTimeEnd != null and scanTimeEnd != ''">and ts.scanrcdCreateddate between #{scanTimeStart} and #{scanTimeEnd}</if>
            <if test="extendParam != null and extendParam !=''">and ( tca.tarcecodeapplyno like CONCAT("%","1","%") or so.orderNo like CONCAT("%","1","%") or d.dictName like CONCAT("%","1","%") )</if>
            <if test="tarcecodeapplyno != null and tarcecodeapplyno !=''">and tca.tarcecodeapplyno = #{tarcecodeapplyno}</if>
            <if test="isReadNoOrderScanLog == false">and ts.orderId > '0'</if>
        </where>
    </select>

    <select id="selectCountOfTssScanrcd" parameterType="SelectCountOfScreenReqVo" resultType="Integer">
        select count(1) from TssScanrcd
        <where>
            <if test="orderId != null  and orderId != ''"> and orderId = #{orderId}</if>
            <if test="tarcecodeapplyId != null  and tarcecodeapplyId != ''"> and tarcecodeapplyId = #{tarcecodeapplyId}</if>
            <if test="stockId != null  and stockId != ''"> and stockId = #{stockId}</if>
            <if test="startTime != null  and startTime != ''"> and scanrcdCreateddate >= #{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and scanrcdCreateddate <![CDATA[ <= ]]> #{endTime}</if>
        </where>
    </select>

    <select id="selectCountByProvince" parameterType="SelectCountOfScreenReqVo" resultType="TssScanrcdStatResVo">
        select count(1) count, s.scanrcdProvince,
        (select z.zoneLatitude from SysZone z where z.zoneProvincezh = s.scanrcdProvince limit 1) zoneLatitude,
        (select z.zoneLongitude from SysZone z where z.zoneProvincezh = s.scanrcdProvince limit 1) zoneLongitude
        from TssScanrcd s
        where s.scanrcdProvince is not null and s.scanrcdCity is not null
        <if test="orderId != null  and orderId != ''"> and s.orderId = #{orderId}</if>
        <if test="tarcecodeapplyId != null  and tarcecodeapplyId != ''"> and s.tarcecodeapplyId = #{tarcecodeapplyId}</if>
        <if test="stockId != null  and stockId != ''"> and s.stockId = #{stockId}</if>
        <if test="startTime != null  and startTime != ''"> and s.scanrcdCreateddate >= #{startTime}</if>
        <if test="endTime != null  and endTime != ''"> and s.scanrcdCreateddate <![CDATA[ <= ]]> #{endTime}</if>
        group by s.scanrcdProvince
    </select>

    <select id="selectCountByCity" parameterType="SelectCountOfScreenReqVo" resultType="TssScanrcdStatResVo">
        select count(1) count, scanrcdCity, scanrcdProvince from TssScanrcd
        where scanrcdProvince is not null and scanrcdCity is not null
        <if test="orderId != null  and orderId != ''"> and orderId = #{orderId}</if>
        <if test="tarcecodeapplyId != null  and tarcecodeapplyId != ''"> and tarcecodeapplyId = #{tarcecodeapplyId}</if>
        <if test="stockId != null  and stockId != ''"> and stockId = #{stockId}</if>
        <if test="startTime != null  and startTime != ''"> and scanrcdCreateddate >= #{startTime}</if>
        <if test="endTime != null  and endTime != ''"> and scanrcdCreateddate <![CDATA[ <= ]]> #{endTime}</if>
        group by scanrcdCity,scanrcdProvince
    </select>

    <insert id="insertTssScanrcd" parameterType="TssScanrcd">
        insert into TssScanrcd
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="scanrcdId != null">scanrcdId,</if>
            <if test="orderId != null">orderId,</if>
            <if test="tarcecodeapplyId != null">tarcecodeapplyId,</if>
            <if test="stockId != null">stockId,</if>
            <if test="scanrcdAddress != null">scanrcdAddress,</if>
            <if test="scanrcdProvince != null">scanrcdProvince,</if>
            <if test="scanrcdCity != null">scanrcdCity,</if>
            <if test="scanrcdCreateddate != null">scanrcdCreateddate,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="scanrcdId != null">#{scanrcdId},</if>
            <if test="orderId != null">#{orderId},</if>
            <if test="tarcecodeapplyId != null">#{tarcecodeapplyId},</if>
            <if test="stockId != null">#{stockId},</if>
            <if test="scanrcdAddress != null">#{scanrcdAddress},</if>
            <if test="scanrcdProvince != null">#{scanrcdProvince},</if>
            <if test="scanrcdCity != null">#{scanrcdCity},</if>
            <if test="scanrcdCreateddate != null">#{scanrcdCreateddate},</if>
         </trim>
    </insert>

    <update id="updateTssScanrcd" parameterType="TssScanrcd">
        update TssScanrcd
        <trim prefix="SET" suffixOverrides=",">
            <if test="orderId != null">orderId = #{orderId},</if>
            <if test="tarcecodeapplyId != null">tarcecodeapplyId = #{tarcecodeapplyId},</if>
            <if test="stockId != null">stockId = #{stockId},</if>
            <if test="scanrcdAddress != null">scanrcdAddress = #{scanrcdAddress},</if>
            <if test="scanrcdProvince != null">scanrcdProvince = #{scanrcdProvince},</if>
            <if test="scanrcdCity != null">scanrcdCity = #{scanrcdCity},</if>
            <if test="scanrcdCreateddate != null">scanrcdCreateddate = #{scanrcdCreateddate},</if>
        </trim>
        where scanrcdId = #{scanrcdId}
    </update>

    <delete id="deleteTssScanrcdByScanrcdId" parameterType="String">
        delete from TssScanrcd where scanrcdId = #{scanrcdId}
    </delete>

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