<?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.ruoyi.asset.mapper.AmsReturnOrderMapper">

    <resultMap type="AmsReturnOrder" id="AmsReturnOrderResult">
        <result property="orderId" column="order_id" />
        <result property="returnNo" column="return_no" />
        <result property="applicantId" column="applicant_id" />
        <result property="applicantName" column="applicant_name" />
        <result property="applyDeptId" column="apply_dept_id" />
        <result property="applyDeptName" column="apply_dept_name" />
        <result property="receiveWarehouseId" column="receive_warehouse_id" />
        <result property="receiveWarehouseCode" column="receive_warehouse_code" />
        <result property="receiveWarehouseName" column="receive_warehouse_name" />
        <result property="confirmUserId" column="confirm_user_id" />
        <result property="confirmUserName" column="confirm_user_name" />
        <result property="confirmTime" column="confirm_time" />
        <result property="orderStatus" column="order_status" />
        <result property="createBy" column="create_by" />
        <result property="createTime" column="create_time" />
        <result property="updateBy" column="update_by" />
        <result property="updateTime" column="update_time" />
        <result property="remark" column="remark" />
        <result property="delFlag" column="del_flag" />
    </resultMap>

    <resultMap id="AmsReturnOrderItemCollectionResult" type="AmsReturnOrder" extends="AmsReturnOrderResult">
        <collection property="amsReturnOrderItemList" ofType="AmsReturnOrderItem"
                    column="order_id" select="selectAmsReturnOrderItemList" />
    </resultMap>

    <resultMap type="AmsReturnOrderItem" id="AmsReturnOrderItemResult">
        <result property="itemId" column="item_id" />
        <result property="orderId" column="order_id" />
        <result property="returnNo" column="return_no" />
        <result property="assetId" column="asset_id" />
        <result property="assetCode" column="asset_code" />
        <result property="assetName" column="asset_name" />
        <result property="categoryId" column="category_id" />
        <result property="categoryCode" column="category_code" />
        <result property="categoryName" column="category_name" />
        <result property="specModel" column="spec_model" />
        <result property="brand" column="brand" />
        <result property="assetStatus" column="asset_status" />
        <result property="beforeUserId" column="before_user_id" />
        <result property="beforeUserName" column="before_user_name" />
        <result property="beforeDeptId" column="before_dept_id" />
        <result property="beforeDeptName" column="before_dept_name" />
        <result property="afterWarehouseId" column="after_warehouse_id" />
        <result property="afterWarehouseCode" column="after_warehouse_code" />
        <result property="afterWarehouseName" column="after_warehouse_name" />
        <result property="afterLocationId" column="after_location_id" />
        <result property="afterLocationCode" column="after_location_code" />
        <result property="afterLocationName" column="after_location_name" />
        <result property="createBy" column="create_by" />
        <result property="createTime" column="create_time" />
        <result property="updateBy" column="update_by" />
        <result property="updateTime" column="update_time" />
        <result property="remark" column="remark" />
        <result property="delFlag" column="del_flag" />
    </resultMap>

    <resultMap type="AmsReturnAssetCandidate" id="AvailableReturnAssetResult">
        <result property="assetId" column="asset_id" />
        <result property="assetCode" column="asset_code" />
        <result property="assetName" column="asset_name" />
        <result property="categoryId" column="category_id" />
        <result property="categoryCode" column="category_code" />
        <result property="categoryName" column="category_name" />
        <result property="specModel" column="spec_model" />
        <result property="brand" column="brand" />
        <result property="assetStatus" column="asset_status" />
        <result property="warehouseId" column="warehouse_id" />
        <result property="warehouseCode" column="warehouse_code" />
        <result property="warehouseName" column="warehouse_name" />
        <result property="locationId" column="location_id" />
        <result property="locationCode" column="location_code" />
        <result property="locationName" column="location_name" />
        <result property="useDeptId" column="use_dept_id" />
        <result property="useDeptName" column="use_dept_name" />
        <result property="useUserId" column="use_user_id" />
        <result property="useUserName" column="use_user_name" />
        <result property="tagCode" column="tag_code" />
        <result property="sourceReceiveOrderId" column="source_receive_order_id" />
        <result property="sourceReceiveItemId" column="source_receive_item_id" />
        <result property="originWarehouseId" column="origin_warehouse_id" />
        <result property="originWarehouseCode" column="origin_warehouse_code" />
        <result property="originWarehouseName" column="origin_warehouse_name" />
        <result property="originWarehouseEnabled" column="origin_warehouse_enabled" />
        <result property="originLocationId" column="origin_location_id" />
        <result property="originLocationCode" column="origin_location_code" />
        <result property="originLocationName" column="origin_location_name" />
        <result property="originLocationEnabled" column="origin_location_enabled" />
    </resultMap>

    <sql id="selectAmsReturnOrderVo">
        select order_id, return_no, applicant_id, applicant_name, apply_dept_id, apply_dept_name,
               receive_warehouse_id, receive_warehouse_code, receive_warehouse_name,
               confirm_user_id, confirm_user_name, confirm_time, order_status,
               create_by, create_time, update_by, update_time, remark, del_flag
        from ams_return_order
    </sql>

    <select id="selectAmsReturnOrderList" parameterType="AmsReturnOrder" resultMap="AmsReturnOrderResult">
        <include refid="selectAmsReturnOrderVo"/>
        <where>
            del_flag = '0'
            <if test="returnNo != null and returnNo != ''">
                and return_no like concat(#{returnNo}, '%')
            </if>
            <if test="applicantName != null and applicantName != ''">
                and applicant_name like concat('%', #{applicantName}, '%')
            </if>
            <if test="applyDeptId != null">
                and apply_dept_id = #{applyDeptId}
            </if>
            <if test="receiveWarehouseId != null">
                and receive_warehouse_id = #{receiveWarehouseId}
            </if>
            <if test="params.beginConfirmTime != null and params.beginConfirmTime != ''">
                and confirm_time &gt;= #{params.beginConfirmTime}
            </if>
            <if test="params.endConfirmTime != null and params.endConfirmTime != ''">
                and confirm_time &lt; date_add(#{params.endConfirmTime}, interval 1 day)
            </if>
            <if test="orderStatus != null and orderStatus != ''">
                and order_status = #{orderStatus}
            </if>
            <if test="params.assetCode != null and params.assetCode != ''">
                and exists (
                    select 1 from ams_return_order_item item
                    where item.order_id = ams_return_order.order_id
                      and item.del_flag = '0'
                      and item.asset_code like concat(#{params.assetCode}, '%')
                )
            </if>
        </where>
        order by create_time desc, order_id desc
    </select>

    <select id="selectAmsReturnOrderByOrderId" parameterType="Long"
            resultMap="AmsReturnOrderItemCollectionResult">
        <include refid="selectAmsReturnOrderVo"/>
        where order_id = #{orderId} and del_flag = '0'
    </select>

    <select id="selectAmsReturnOrderByOrderIdForUpdate" parameterType="Long"
            resultMap="AmsReturnOrderItemCollectionResult">
        <include refid="selectAmsReturnOrderVo"/>
        where order_id = #{orderId} and del_flag = '0'
        for update
    </select>

    <select id="selectAvailableReturnAssetList" resultMap="AvailableReturnAssetResult">
        select asset.asset_id, asset.asset_code, asset.asset_name, asset.category_id,
               asset.category_code, asset.category_name, asset.spec_model, asset.brand,
               asset.asset_status, asset.warehouse_id, asset.warehouse_code, asset.warehouse_name,
               asset.location_id, asset.location_code, asset.location_name, asset.use_dept_id,
               asset.use_dept_name, asset.use_user_id, asset.use_user_name, asset.tag_code,
               origin_order.order_id as source_receive_order_id,
               origin_item.item_id as source_receive_item_id,
               origin_item.before_warehouse_id as origin_warehouse_id,
               origin_item.before_warehouse_code as origin_warehouse_code,
               origin_item.before_warehouse_name as origin_warehouse_name,
               origin_warehouse.enabled as origin_warehouse_enabled,
               origin_item.before_location_id as origin_location_id,
               origin_item.before_location_code as origin_location_code,
               origin_item.before_location_name as origin_location_name,
               origin_location.enabled as origin_location_enabled
        from ams_asset asset
        left join ams_receive_order_item origin_item on origin_item.item_id = (
            select candidate_item.item_id
            from ams_receive_order_item candidate_item
            inner join ams_receive_order candidate_order on candidate_order.order_id = candidate_item.order_id
                and candidate_order.del_flag = '0'
                and candidate_order.order_status = 'COMPLETED'
            where candidate_item.asset_id = asset.asset_id
              and candidate_item.del_flag = '0'
            order by candidate_order.confirm_time desc, candidate_order.order_id desc, candidate_item.item_id desc
            limit 1
        )
        left join ams_receive_order origin_order on origin_order.order_id = origin_item.order_id
        left join ams_warehouse origin_warehouse on origin_warehouse.warehouse_id = origin_item.before_warehouse_id
            and origin_warehouse.del_flag = '0'
        left join ams_asset_location origin_location on origin_location.location_id = origin_item.before_location_id
            and origin_location.del_flag = '0'
        where asset.del_flag = '0'
          and asset.asset_status = #{inUseStatus}
          and not exists (
              select 1
              from ams_return_order_item item
              inner join ams_return_order return_order on return_order.order_id = item.order_id
                  and return_order.del_flag = '0'
                  and return_order.order_status in (#{draftStatus}, #{pendingStatus})
              where item.asset_id = asset.asset_id
                and item.del_flag = '0'
                <if test="currentOrderId != null">
                    and return_order.order_id != #{currentOrderId}
                </if>
          )
        <if test="asset.assetCode != null and asset.assetCode != ''">
            and asset.asset_code like concat('%', #{asset.assetCode}, '%')
        </if>
        <if test="asset.assetName != null and asset.assetName != ''">
            and asset.asset_name like concat('%', #{asset.assetName}, '%')
        </if>
        <if test="asset.categoryName != null and asset.categoryName != ''">
            and asset.category_name like concat('%', #{asset.categoryName}, '%')
        </if>
        <if test="asset.useDeptId != null">
            and asset.use_dept_id = #{asset.useDeptId}
        </if>
        <if test="asset.useUserName != null and asset.useUserName != ''">
            and asset.use_user_name like concat('%', #{asset.useUserName}, '%')
        </if>
        <if test="originWarehouseId != null">
            and origin_item.before_warehouse_id = #{originWarehouseId}
            and origin_item.before_location_id is not null
        </if>
        <if test="missingOriginOnly != null and missingOriginOnly">
            and (origin_item.before_warehouse_id is null or origin_item.before_location_id is null)
        </if>
        order by asset.asset_id
    </select>

    <select id="selectReturnAssetCandidateByAssetId" parameterType="Long" resultMap="AvailableReturnAssetResult">
        select asset.asset_id, asset.asset_code, asset.asset_name, asset.category_id,
               asset.category_code, asset.category_name, asset.spec_model, asset.brand,
               asset.asset_status, asset.warehouse_id, asset.warehouse_code, asset.warehouse_name,
               asset.location_id, asset.location_code, asset.location_name, asset.use_dept_id,
               asset.use_dept_name, asset.use_user_id, asset.use_user_name, asset.tag_code,
               origin_order.order_id as source_receive_order_id,
               origin_item.item_id as source_receive_item_id,
               origin_item.before_warehouse_id as origin_warehouse_id,
               origin_item.before_warehouse_code as origin_warehouse_code,
               origin_item.before_warehouse_name as origin_warehouse_name,
               origin_warehouse.enabled as origin_warehouse_enabled,
               origin_item.before_location_id as origin_location_id,
               origin_item.before_location_code as origin_location_code,
               origin_item.before_location_name as origin_location_name,
               origin_location.enabled as origin_location_enabled
        from ams_asset asset
        left join ams_receive_order_item origin_item on origin_item.item_id = (
            select candidate_item.item_id
            from ams_receive_order_item candidate_item
            inner join ams_receive_order candidate_order on candidate_order.order_id = candidate_item.order_id
                and candidate_order.del_flag = '0'
                and candidate_order.order_status = 'COMPLETED'
            where candidate_item.asset_id = asset.asset_id
              and candidate_item.del_flag = '0'
            order by candidate_order.confirm_time desc, candidate_order.order_id desc, candidate_item.item_id desc
            limit 1
        )
        left join ams_receive_order origin_order on origin_order.order_id = origin_item.order_id
        left join ams_warehouse origin_warehouse on origin_warehouse.warehouse_id = origin_item.before_warehouse_id
            and origin_warehouse.del_flag = '0'
        left join ams_asset_location origin_location on origin_location.location_id = origin_item.before_location_id
            and origin_location.del_flag = '0'
        where asset.asset_id = #{assetId}
          and asset.del_flag = '0'
    </select>

    <select id="countOtherActiveReturnOrderByAssetId" resultType="int">
        select count(1)
        from ams_return_order_item item
        inner join ams_return_order return_order on return_order.order_id = item.order_id
            and return_order.del_flag = '0'
            and return_order.order_status in (#{draftStatus}, #{pendingStatus})
        where item.asset_id = #{assetId}
          and item.del_flag = '0'
          <if test="currentOrderId != null">
              and return_order.order_id != #{currentOrderId}
          </if>
    </select>

    <select id="selectAmsReturnOrderItemList" resultMap="AmsReturnOrderItemResult">
        select item.item_id, item.order_id, item.return_no, item.asset_id, item.asset_code,
               item.asset_name, item.category_id, item.category_code, item.category_name,
               item.spec_model, item.brand, asset.asset_status, item.before_user_id,
               item.before_user_name, item.before_dept_id, item.before_dept_name,
               item.after_warehouse_id, item.after_warehouse_code, item.after_warehouse_name,
               item.after_location_id, item.after_location_code, item.after_location_name,
               item.create_by, item.create_time, item.update_by, item.update_time,
               item.remark, item.del_flag
        from ams_return_order_item item
        left join ams_asset asset on asset.asset_id = item.asset_id and asset.del_flag = '0'
        where item.order_id = #{order_id} and item.del_flag = '0'
        order by item.item_id
    </select>

    <insert id="insertAmsReturnOrder" parameterType="AmsReturnOrder"
            useGeneratedKeys="true" keyProperty="orderId">
        insert into ams_return_order (
            return_no, applicant_id, applicant_name, apply_dept_id, apply_dept_name,
            receive_warehouse_id, receive_warehouse_code, receive_warehouse_name,
            order_status, create_by, create_time, remark, del_flag
        ) values (
            #{returnNo}, #{applicantId}, #{applicantName}, #{applyDeptId}, #{applyDeptName},
            #{receiveWarehouseId}, #{receiveWarehouseCode}, #{receiveWarehouseName},
            #{orderStatus}, #{createBy}, #{createTime}, #{remark}, #{delFlag}
        )
    </insert>

    <update id="updateAmsReturnOrder" parameterType="AmsReturnOrder">
        update ams_return_order
        set receive_warehouse_id = #{receiveWarehouseId},
            receive_warehouse_code = #{receiveWarehouseCode},
            receive_warehouse_name = #{receiveWarehouseName},
            update_by = #{updateBy},
            update_time = #{updateTime},
            remark = #{remark}
        where order_id = #{orderId} and del_flag = '0' and order_status = 'DRAFT'
    </update>

    <update id="submitAmsReturnOrder" parameterType="AmsReturnOrder">
        update ams_return_order
        set order_status = #{orderStatus},
            update_by = #{updateBy},
            update_time = #{updateTime}
        where order_id = #{orderId} and del_flag = '0' and order_status = 'DRAFT'
    </update>

    <update id="confirmAmsReturnOrder" parameterType="AmsReturnOrder">
        update ams_return_order
        set order_status = #{orderStatus},
            confirm_user_id = #{confirmUserId},
            confirm_user_name = #{confirmUserName},
            confirm_time = #{confirmTime},
            update_by = #{updateBy},
            update_time = #{updateTime}
        where order_id = #{orderId} and del_flag = '0' and order_status = 'PENDING_CONFIRM'
    </update>

    <update id="deleteAmsReturnOrderByOrderId" parameterType="Long">
        update ams_return_order
        set del_flag = '1'
        where order_id = #{orderId} and del_flag = '0' and order_status = 'DRAFT'
    </update>

    <update id="deleteAmsReturnOrderByOrderIds" parameterType="String">
        update ams_return_order
        set del_flag = '1'
        where order_id in
        <foreach item="orderId" collection="array" open="(" separator="," close=")">
            #{orderId}
        </foreach>
        and del_flag = '0' and order_status = 'DRAFT'
    </update>

    <update id="deleteAmsReturnOrderItemByOrderIds" parameterType="String">
        update ams_return_order_item
        set del_flag = '1'
        where order_id in
        <foreach item="orderId" collection="array" open="(" separator="," close=")">
            #{orderId}
        </foreach>
        and del_flag = '0'
    </update>

    <update id="deleteAmsReturnOrderItemByOrderId" parameterType="Long">
        update ams_return_order_item
        set del_flag = '1'
        where order_id = #{orderId} and del_flag = '0'
    </update>

    <insert id="batchAmsReturnOrderItem">
        insert into ams_return_order_item (
            order_id, return_no, asset_id, asset_code, asset_name, category_id, category_code,
            category_name, spec_model, brand, before_user_id, before_user_name, before_dept_id,
            before_dept_name, after_warehouse_id, after_warehouse_code, after_warehouse_name,
            after_location_id, after_location_code, after_location_name,
            create_by, create_time, remark, del_flag
        ) values
        <foreach item="item" collection="list" separator=",">
            (
                #{item.orderId}, #{item.returnNo}, #{item.assetId}, #{item.assetCode},
                #{item.assetName}, #{item.categoryId}, #{item.categoryCode}, #{item.categoryName},
                #{item.specModel}, #{item.brand}, #{item.beforeUserId}, #{item.beforeUserName},
                #{item.beforeDeptId}, #{item.beforeDeptName}, #{item.afterWarehouseId},
                #{item.afterWarehouseCode}, #{item.afterWarehouseName}, #{item.afterLocationId},
                #{item.afterLocationCode}, #{item.afterLocationName}, #{item.createBy},
                #{item.createTime}, #{item.remark}, #{item.delFlag}
            )
        </foreach>
    </insert>

</mapper>
