<?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="cn.ezhizao.project.business.mapper.BizCertificateLotMapper">

    <resultMap type="cn.ezhizao.project.business.domain.BizCertificateLot" id="BizCertificateLotResult">
        <id column="id" property="id"/>
    </resultMap>


    <select id="getList" parameterType="BizCertificateLot" resultMap="BizCertificateLotResult">
        SELECT * FROM biz_certificate_lot
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            deleted = 0
            <if test="startCode != null  and startCode != ''"> AND start_code = #{startCode}</if>
            <if test="endCode != null  and endCode != ''"> AND end_code = #{endCode}</if>
            <if test="printDate != null "> AND print_date = #{printDate}</if>
            <if test="qrcode != null  and qrcode != ''"> AND qrcode = #{qrcode}</if>
            <if test="qrcodeUrl != null  and qrcodeUrl != ''"> AND qrcode_url = #{qrcodeUrl}</if>
            <if test="barcode != null  and barcode != ''"> AND barcode = #{barcode}</if>
            <if test="barcodeUrl != null  and barcodeUrl != ''"> AND barcode_url = #{barcodeUrl}</if>
        </trim>
    </select>
    <select id="getTaskStockList" resultType="cn.ezhizao.project.business.domain.BizTaksStockCertificateLot">
        SELECT t1.tenant_id,t1.certificate_id,t1.sort,t1.start_code,t1.end_code,t1.qrcode,t1.qrcode_url,t1.barcode,t1.barcode_url,t2.print_date,t2.product_id,t2.product_code,t2.product_description,t2.drawing_number,t2.dept_id,t2.dept_name,t2.print_time,t2.company_id,t2.lot_id,t2.lot_code,t2.number as tempProdNum
        FROM `biz_certificate_lot` t1
        JOIN biz_certificate t2
        ON t1.certificate_id = t2.id
        AND t2.deleted = 0
        WHERE t1.deleted = 0
        AND (
        t2.lot_code NOT IN (SELECT lot_code FROM biz_inbound_order_detail WHERE deleted = 0)
        OR t2.lot_code NOT IN (SELECT lot_code FROM biz_inbound_order_detail WHERE deleted = 0 AND is_inbound = 1)
        );

    </select>

    <delete id="physicalDelete">
        DELETE FROM biz_certificate_lot
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            <if test="id != null">
                id = #{id} AND
            </if>
       <!-- 删除条件为其他外键可以在这里加 -->
        </trim>
    </delete>
</mapper>
