<?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.deer.wms.base.system.dao.bill.BillOutMasterMapper">
    
    <resultMap type="BillOutMaster" id="BillOutMasterResult">
        <result property="billId"    column="bill_id"    />
        <result property="billNo"    column="bill_no"    />
        <result property="contractNo"    column="contract_no"    />
        <result property="createTime"    column="create_time"    />
        <result property="createUserName"    column="create_user_name"    />
        <result property="createUserId"    column="create_user_id"    />
        <result property="accountAliasId"    column="account_alias_id"    />
        <result property="state"    column="state"    />
        <result property="memo"    column="memo"    />
        <result property="wareId"    column="ware_id"    />
        <result property="type"    column="type"    />
        <result property="userNameTwo"    column="user_name_two"    />
        <result property="userIdTwo"    column="user_id_two"    />
    </resultMap>

    <resultMap type="com.deer.wms.base.system.model.bill.BillOutMasterDto"   id="BillOutMasterDtoResult"  extends="BillOutMasterResult">
        <result property="wareName"   jdbcType="VARCHAR" column="ware_name"    />
        <result property="accountAlias" jdbcType="VARCHAR" column="account_alias"/>
        <result property="itemCode" jdbcType="VARCHAR" column="item_code"/>
        <result property="quantity" jdbcType="INTEGER" column="quantity"/>
        <result property="priority" jdbcType="VARCHAR" column="priority"/>
        <result property="alreadyOutQuantity" jdbcType="INTEGER" column="already_out_quantity"/>
        <result property="billOutDetailId"  jdbcType="INTEGER"  column="bill_out_detail_id"    />
        <result property="sequence" jdbcType="INTEGER" column="sequence"/>
        <result property="taskId" jdbcType="VARCHAR" column="task_id"/>
        <result property="finishedCode" jdbcType="VARCHAR" column="finished_code"/>
    </resultMap>

    <select id="findListTwo" parameterType="com.deer.wms.base.system.model.bill.BillOutMasterCriteria" resultMap="BillOutMasterDtoResult">
        select
        bom.*,
        bod.bill_out_detail_id,
        bod.item_code,
        bod.quantity,
        bod.priority,
        bod.sequence,
        bod.already_out_quantity
        from bill_out_master bom
        join bill_out_detail bod on bod.bill_id = bom.bill_id
        where 1=1
        and bom.state in(1,0)
        and bom.type = 1
        <if test="billNo != null and billNo != ''">
            and bom.bill_no = #{billNo}
        </if>
        <if test="state != null and state != -1">
            and bom.state = #{state}
        </if>
        <if test="orderByState != null and orderByState == 101">
            and bod.already_out_quantity &lt; bod.quantity
        </if>
        <if test="itemCode != null and itemCode != ''">
            and bod.item_code = #{itemCode}
        </if>
        <if test="orderByState != null and orderByState == 102">
            and bod.sequence &gt; #{sequence}
        </if>
        order by bom.bill_id
    </select>


    <sql id="selectBillOutMasterVo">
        select bill_id, bill_no, contract_no, create_time, create_user_name,create_user_id, state, memo,ware_id from bill_out_master
    </sql>

    <select id="findBillOutMasterDtoByBillId" parameterType="Integer" resultMap="BillOutMasterDtoResult">
        select bom.bill_id,
             bom.bill_no,
             bom.contract_no,
             bom.create_time,
             bom.create_user_name,
             bom.create_user_id,
             bom.state,
             bom.memo,
            bom.ware_id,
            wi.ware_name
        from bill_out_master bom join ware_info wi
        on bom.ware_id = wi.ware_id
        where bom.bill_id = #{billId}
    </select>

    <delete id="deleteBillOutMasterById" parameterType="Integer">
        delete from bill_out_master where bill_id = #{billId}
    </delete>

    <delete id="deleteBillOutMasterByIds" parameterType="String">
        delete from bill_out_master where bill_id in
        <foreach item="billId" collection="array" open="(" separator="," close=")">
            #{billId}
        </foreach>
    </delete>

    <update id="updateBillOutMaster" parameterType="BillOutMaster">
        update bill_out_master
        <trim prefix="SET" suffixOverrides=",">
            <if test="billNo != null  ">bill_no = #{billNo},</if>
            <if test="contractNo != null  and contractNo != ''  ">contract_no = #{contractNo},</if>
            <if test="createTime != null  ">create_time = #{createTime},</if>
            <if test="createUserId != null  ">create_user_id = #{createUserId},</if>
            <if test="createUserName != null  ">create_user_name=#{createUserName},</if>
            <if test="wareId != null  ">ware_id=#{wareId},</if>
            <if test="state != null  ">state = #{state},</if>
            <if test="memo != null  and memo != ''  ">memo = #{memo},</if>
        </trim>
        where bill_id = #{billId}
    </update>

    <select id="selectBillOutMasterById" parameterType="Integer" resultMap="BillOutMasterResult">
        <include refid="selectBillOutMasterVo"/>
        where bill_id = #{billId}
    </select>

    <insert id="saveBillOutMaster" parameterType="billOutMaster" useGeneratedKeys="true" keyColumn="bill_id" keyProperty="billId">

        insert into bill_out_master values
          (null, #{billOutMaster.billNo}, #{billOutMaster.contractNo}, #{billOutMaster.createTime},
            #{billOutMaster.createUserName}, #{billOutMaster.createUserId}, #{billOutMaster.state},
              #{billOutMaster.memo}, #{billOutMaster.wareId})

    </insert>

    <select id="selectBillOutMasterList" parameterType="com.deer.wms.base.system.model.bill.BillOutMasterCriteria" resultMap="BillOutMasterDtoResult">
        select
        bom.bill_no,
        bom.create_time,
        bom.create_user_name,
        bom.memo,
        bom.state,
        bom.type,
        aa.account_alias,
        bod.bill_out_detail_id,
        bod.item_code,
        bod.quantity,
        bod.priority,
        bod.already_out_quantity
        from bill_out_master bom
        join bill_out_detail bod on bod.bill_id = bom.bill_id
        left join account_alias aa on aa.disposition_id = bom.account_alias_id
        where 1=1
        <if test="itemCode != null and itemCode != ''">
            and bod.item_code = #{itemCode}
        </if>
        <if test="type != null and type != -1">
            and bom.type = #{type}
        </if>
        <if test="state != null and state != -1">
            and bom.state = #{state}
        </if>
        <if test="billNo != null and billNo != ''">
            and bom.bill_no = #{billNo}
        </if>

        order by bod.bill_out_detail_id desc
    </select>

    <select id="selectBillOutMasterByBillOutDetailId" parameterType="Integer" resultMap="BillOutMasterResult">
        SELECT
        	bom.*
        FROM
        	bill_out_master bom
        INNER JOIN bill_out_detail bod ON bod.bill_id = bom.bill_id
        WHERE
        	bod.bill_out_detail_id = #{billOutDetailId}
    </select>

    <select id="findList" parameterType="com.deer.wms.base.system.model.bill.BillOutMasterCriteria" resultMap="BillOutMasterDtoResult">
        select
        bom.*,
        bod.bill_out_detail_id,
        bod.item_code,
        bod.quantity,
        bod.priority,
        bod.sequence,
        bod.task_id,
        bod.finished_code,
        bod.already_out_quantity
        from bill_out_master bom
        join bill_out_detail bod on bod.bill_id = bom.bill_id
        where 1=1
        <if test="billNo != null and billNo != ''">
            and bom.bill_no = #{billNo}
        </if>
        <if test="type != null">
            and bom.type = #{type}
        </if>
        <if test="state != null and state != -1">
            and bom.state = #{state}
        </if>
        <if test="orderByState != null and orderByState == 101">
            and bod.already_out_quantity &lt; bod.quantity
            and bom.state in (1,0)
        </if>
        <if test="billIds != null">
            and bom.bill_id in
            <foreach collection="billIds" item="billId" index="index" open="(" separator="," close=")">
                #{billId}
            </foreach>
        </if>
        <if test="itemCode != null and itemCode != ''">
            and bod.item_code = #{itemCode}
        </if>
        <if test="orderByState != null and orderByState == 102">
            and bod.sequence &gt; #{sequence}
        </if>
        <if test="orderByState != null and orderByState == 103">
            and bom.state in (1,0,2)
        </if>
        order by bod.sequence
    </select>

    <select id="totalAll" resultType="java.lang.Integer">
        select count(0) from bill_out_master
    </select>

    <select id="totalNoFinish" resultType="java.lang.Integer">
        select count(0) from bill_out_master where state != 2;
    </select>

</mapper>















