<?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.lframework.xingyun.sc.mappers.SerialNumberMapper">

    <delete id="deleteByAdjustId">
        DELETE FROM tbl_serial_number WHERE adjust_id=#{id}
    </delete>


    <delete id="deleteByAdjustIds">
        DELETE FROM tbl_serial_number
        WHERE adjust_id IN
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <update id="updateByAdjustId">
        update tbl_serial_number set is_examine=1 where adjust_id=#{id}
    </update>

    <select id="getSerialNumberCount"  resultType="java.lang.Integer">
        SELECT count(0) FROM tbl_serial_number
        WHERE product_id = #{productId}
        AND is_sell = 0
        AND is_examine = 1
        AND delete_flag = 0
        <if test="id != null and id != ''">
            AND id =#{id}
        </if>
        <if test="serialNumber != null and serialNumber != ''">
            AND serial_number LIKE CONCAT('%', #{serialNumber}, '%')
        </if>
    </select>

    <select id="getSerialNumberList"  resultType="com.lframework.xingyun.sc.entity.SerialNumber">
        SELECT * FROM tbl_serial_number
        WHERE product_id = #{productId}
        AND is_sell = 0
        AND is_examine = 1
        AND delete_flag = 0
        <if test="id != null and id != ''">
            AND id =#{id}
        </if>
        <if test="serialNumber != null and serialNumber != ''">
            AND serial_number LIKE CONCAT('%', #{serialNumber}, '%')
        </if>
        <if test="startNum != null and pageSize != null">
            LIMIT #{startNum}, #{pageSize}
        </if>
    </select>

    <select id="getSerialNumberByProductId"  resultType="com.lframework.xingyun.sc.entity.SerialNumber">
        SELECT * FROM tbl_serial_number
        WHERE product_id=#{productId}
        AND is_sell = 0
        AND is_examine = 1
        AND delete_flag = 0
    </select>

    <update id="updateByProductId">
        update tbl_serial_number set delete_flag=1 where id=#{id}
    </update>

    <update id="updateByOutBillNo">
        update tbl_serial_number set is_sell=1 where out_bill_no=#{outBillNo}
    </update>

    <update id="updatePassByOutBillNo">
        update tbl_serial_number set is_sell=NULL where out_bill_no=#{outBillNo}
    </update>

    <update id="updatePassBySerialNumber">
        update tbl_serial_number set is_sell=1,out_bill_no=#{outBillNo} where serial_number=#{serialNumber}
    </update>

    <select id="getSerialNumberByInBillNo"  resultType="com.lframework.xingyun.sc.entity.SerialNumber">
        SELECT * FROM tbl_serial_number WHERE in_bill_no=#{inBillNo};
    </select>

    <select id="getSerialByOutBillNo"  resultType="java.lang.String">
        SELECT serial_number FROM tbl_serial_number WHERE out_bill_no=#{outBillNo} AND product_id=#{productId};
    </select>

    <select id="getSerialIdByOutBillNo"  resultType="java.lang.String">
        SELECT id FROM tbl_serial_number WHERE out_bill_no=#{outBillNo} AND product_id=#{productId};
    </select>

    <select id="getSerialIdByInBillNo"  resultType="java.lang.String">
        SELECT id FROM tbl_serial_number WHERE in_bill_no=#{outBillNo} AND product_id=#{productId};
    </select>

    <select id="getSerialByInBillNo"  resultType="java.lang.String">
        SELECT serial_number FROM tbl_serial_number WHERE in_bill_no=#{inBillNo} AND product_id=#{productId};
    </select>

    <update id="updateOutBillNoClean">
        update tbl_serial_number set out_bill_no=NULL WHERE id=#{id}
    </update>

    <update id="updateInBillNoClean">
        update tbl_serial_number set in_bill_no=NULL WHERE id=#{id}
    </update>

    <update id="updateOutBillNo">
        update tbl_serial_number set out_bill_no=#{outBillNo} WHERE id=#{id}
    </update>

    <update id="updateInBillNo">
        update tbl_serial_number set in_bill_no=#{inBillNo} WHERE id=#{id}
    </update>


</mapper>
