<?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.iocoder.yudao.module.system.dal.mysql.houselocation.WareHouseLocationMapper">

    <resultMap id="BaseResultMap" type="cn.iocoder.yudao.module.system.dal.dataobject.houselocation.WareHouseLocationDO">
        <!--@Table ware_house_location-->
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="laneId" column="lane_id" jdbcType="INTEGER"/>
        <result property="laneName" column="lane_name" jdbcType="VARCHAR"/>
        <result property="areaId" column="area_id" jdbcType="INTEGER"/>
        <result property="areaName" column="area_name" jdbcType="VARCHAR"/>
        <result property="locationNo" column="location_no" jdbcType="VARCHAR"/>
        <result property="locationYaw" column="location_yaw" jdbcType="VARCHAR"/>
        <result property="groupName" column="group_name" jdbcType="VARCHAR"/>
        <result property="skuInfo" column="sku_info" jdbcType="VARCHAR"/>
        <result property="skuBatch" column="sku_batch" jdbcType="VARCHAR"/>
        <result property="skuNumber" column="sku_number" jdbcType="INTEGER"/>
        <result property="trayInfo" column="tray_info" jdbcType="VARCHAR"/>
        <result property="locationEnable" column="location_enable" jdbcType="INTEGER"/>
        <result property="locationLock" column="location_lock" jdbcType="INTEGER"/>
        <result property="locationUseStatus" column="location_use_status" jdbcType="INTEGER"/>
        <result property="locationX" column="location_x" jdbcType="VARCHAR"/>
        <result property="locationY" column="location_y" jdbcType="VARCHAR"/>
        <result property="locationWide" column="location_wide" jdbcType="NUMERIC"/>
        <result property="locationDeep" column="location_deep" jdbcType="NUMERIC"/>
        <result property="locationHeight" column="location_height" jdbcType="NUMERIC"/>
        <result property="locationDefaultHeight" column="location_default_height" jdbcType="NUMERIC"/>
        <result property="locationTotalHeight" column="location_total_height" jdbcType="NUMERIC"/>
        <result property="locationTrayHeight" column="location_tray_height" jdbcType="NUMERIC"/>
        <result property="locationStorey" column="location_storey" jdbcType="INTEGER"/>
        <result property="locationType" column="location_type" jdbcType="INTEGER"/>
        <result property="locationNumber" column="location_number" jdbcType="INTEGER"/>
        <result property="mapId" column="map_id" jdbcType="INTEGER"/>
        <result property="mapItemId" column="map_item_id" jdbcType="INTEGER"/>
        <result property="creator" column="creator" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updater" column="updater" jdbcType="VARCHAR"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="deleted" column="deleted" jdbcType="INTEGER"/>
    </resultMap>



    <!--查询单个-->
    <sql id="base_sql" >
               id,
               lane_id,
               lane_name,
               area_id,
               area_name,
               location_no,
               location_yaw,
               group_name,
               sku_info,
               sku_batch,
               sku_number,
               tray_info,
               location_enable,
               location_lock,
               location_use_status,
               location_x,
               location_y,
               location_wide,
               location_deep,
               location_height,
               location_default_height,
               location_total_height,
               location_tray_height,
               location_storey,
               location_type,
               location_number,
               map_id,
               map_item_id,
               creator,
               create_time,
               updater,
               update_time,
               deleted
    </sql>
    <update id="deleteHouseLocationByMapId">
        update
            ware_house_location
        set
            deleted = 1
        where
            map_id = #{mapId}
    </update>

    <!--查询指定行数据-->
    <select id="queryAllByLimit" resultMap="BaseResultMap">
        select
            <include refid="base_sql"></include>
        from ware_house_location
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="laneId != null">
                and lane_id = #{laneId}
            </if>
            <if test="laneName != null and laneName != ''">
                and lane_name = #{laneName}
            </if>
            <if test="areaId != null">
                and area_id = #{areaId}
            </if>
            <if test="areaName != null and areaName != ''">
                and area_name = #{areaName}
            </if>
            <if test="locationNo != null and locationNo != ''">
                and location_no = #{locationNo}
            </if>
            <if test="locationYaw != null and locationYaw != ''">
                and location_yaw = #{locationYaw}
            </if>
            <if test="groupName != null and groupName != ''">
                and group_name = #{groupName}
            </if>
            <if test="skuInfo != null and skuInfo != ''">
                and sku_info = #{skuInfo}
            </if>
            <if test="skuBatch != null and skuBatch != ''">
                and sku_batch = #{skuBatch}
            </if>
            <if test="skuNumber != null">
                and sku_number = #{skuNumber}
            </if>
            <if test="trayInfo != null and trayInfo != ''">
                and tray_info = #{trayInfo}
            </if>
            <if test="locationEnable != null">
                and location_enable = #{locationEnable}
            </if>
            <if test="locationLock != null">
                and location_lock = #{locationLock}
            </if>
            <if test="locationUseStatus != null">
                and location_use_status = #{locationUseStatus}
            </if>
            <if test="locationX != null and locationX != ''">
                and location_x = #{locationX}
            </if>
            <if test="locationY != null and locationY != ''">
                and location_y = #{locationY}
            </if>
            <if test="locationWide != null">
                and location_wide = #{locationWide}
            </if>
            <if test="locationDeep != null">
                and location_deep = #{locationDeep}
            </if>
            <if test="locationHeight != null">
                and location_height = #{locationHeight}
            </if>
            <if test="locationDefaultHeight != null">
                and location_default_height = #{locationDefaultHeight}
            </if>
            <if test="locationTotalHeight != null">
                and location_total_height = #{locationTotalHeight}
            </if>
            <if test="locationTrayHeight != null">
                and location_tray_height = #{locationTrayHeight}
            </if>
            <if test="locationStorey != null">
                and location_storey = #{locationStorey}
            </if>
            <if test="locationType != null">
                and location_type = #{locationType}
            </if>
            <if test="locationNumber != null">
                and location_number = #{locationNumber}
            </if>
            <if test="creator != null and creator != ''">
                and creator = #{creator}
            </if>
            <if test="createTime != null">
                and create_time = #{createTime}
            </if>
            <if test="updater != null and updater != ''">
                and updater = #{updater}
            </if>
            <if test="updateTime != null">
                and update_time = #{updateTime}
            </if>
            <if test="1 == 1 ">
                and deleted = '0'
            </if>
        </where>
        limit 1;
    </select>

    <!--通过实体作为筛选条件查询-->
    <select id="queryAll" resultMap="BaseResultMap">
    select
    id, lane_id, lane_name, area_id, area_name, location_no, location_yaw, group_name, sku_info, sku_batch, sku_number,
    tray_info, location_enable, location_lock, location_use_status, location_x, location_y, location_wide,
    location_deep, location_height, location_default_height, location_total_height, location_tray_height,
    location_storey, location_type, location_number, creator, create_time, updater, update_time, deleted, tenant_id
    from ware_house_location
    </select>
    <!--统计总行数-->
    <select id="count" resultType="java.lang.Long">
        select count(1)
        from ware_house_location
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="laneId != null">
                and lane_id = #{laneId}
            </if>
            <if test="laneName != null and laneName != ''">
                and lane_name = #{laneName}
            </if>
            <if test="areaId != null">
                and area_id = #{areaId}
            </if>
            <if test="areaName != null and areaName != ''">
                and area_name = #{areaName}
            </if>
            <if test="locationNo != null and locationNo != ''">
                and location_no = #{locationNo}
            </if>
            <if test="locationYaw != null and locationYaw != ''">
                and location_yaw = #{locationYaw}
            </if>
            <if test="groupName != null and groupName != ''">
                and group_name = #{groupName}
            </if>
            <if test="skuInfo != null and skuInfo != ''">
                and sku_info = #{skuInfo}
            </if>
            <if test="skuBatch != null and skuBatch != ''">
                and sku_batch = #{skuBatch}
            </if>
            <if test="skuNumber != null">
                and sku_number = #{skuNumber}
            </if>
            <if test="trayInfo != null and trayInfo != ''">
                and tray_info = #{trayInfo}
            </if>
            <if test="locationEnable != null">
                and location_enable = #{locationEnable}
            </if>
            <if test="locationLock != null">
                and location_lock = #{locationLock}
            </if>
            <if test="locationUseStatus != null">
                and location_use_status = #{locationUseStatus}
            </if>
            <if test="locationX != null and locationX != ''">
                and location_x = #{locationX}
            </if>
            <if test="locationY != null and locationY != ''">
                and location_y = #{locationY}
            </if>
            <if test="locationWide != null">
                and location_wide = #{locationWide}
            </if>
            <if test="locationDeep != null">
                and location_deep = #{locationDeep}
            </if>
            <if test="locationHeight != null">
                and location_height = #{locationHeight}
            </if>
            <if test="locationDefaultHeight != null">
                and location_default_height = #{locationDefaultHeight}
            </if>
            <if test="locationTotalHeight != null">
                and location_total_height = #{locationTotalHeight}
            </if>
            <if test="locationTrayHeight != null">
                and location_tray_height = #{locationTrayHeight}
            </if>
            <if test="locationStorey != null">
                and location_storey = #{locationStorey}
            </if>
            <if test="locationType != null">
                and location_type = #{locationType}
            </if>
            <if test="locationNumber != null">
                and location_number = #{locationNumber}
            </if>
            <if test="creator != null and creator != ''">
                and creator = #{creator}
            </if>
            <if test="createTime != null">
                and create_time = #{createTime}
            </if>
            <if test="updater != null and updater != ''">
                and updater = #{updater}
            </if>
            <if test="updateTime != null">
                and update_time = #{updateTime}
            </if>
            <if test="deleted != null">
                and deleted = #{deleted}
            </if>
            <if test="tenantId != null">
                and tenant_id = #{tenantId}
            </if>
        </where>
    </select>

    <!--新增所有列-->
    <insert id="insertSelect" keyProperty="id" useGeneratedKeys="true">
        insert into ware_house_location(lane_id, lane_name, area_id, area_name, location_no, location_yaw, group_name,
                                        sku_info, sku_batch, sku_number, tray_info, location_enable, location_lock,
                                        location_use_status, location_x, location_y, location_wide, location_deep,
                                        location_height, location_default_height, location_total_height,
                                        location_tray_height, location_storey, location_type, location_number, creator,
                                        create_time, updater, update_time, deleted, tenant_id)
        values (#{laneId}, #{laneName}, #{areaId}, #{areaName}, #{locationNo}, #{locationYaw}, #{groupName}, #{skuInfo},
                #{skuBatch}, #{skuNumber}, #{trayInfo}, #{locationEnable}, #{locationLock}, #{locationUseStatus},
                #{locationX}, #{locationY}, #{locationWide}, #{locationDeep}, #{locationHeight},
                #{locationDefaultHeight}, #{locationTotalHeight}, #{locationTrayHeight}, #{locationStorey},
                #{locationType}, #{locationNumber}, #{creator}, #{createTime}, #{updater}, #{updateTime}, #{deleted},
                #{tenantId})
    </insert>

    <insert id="insertBatch" keyProperty="id" useGeneratedKeys="true">
        insert into ware_house_location(lane_id, lane_name, area_id, area_name, location_no, location_yaw, group_name,
        sku_info, sku_batch, sku_number, tray_info, location_enable, location_lock, location_use_status, location_x,
        location_y, location_wide, location_deep, location_height, location_default_height, location_total_height,
        location_tray_height, location_storey, location_type, location_number, creator, create_time, updater,
        update_time, deleted, tenant_id)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.laneId}, #{entity.laneName}, #{entity.areaId}, #{entity.areaName}, #{entity.locationNo},
            #{entity.locationYaw}, #{entity.groupName}, #{entity.skuInfo}, #{entity.skuBatch}, #{entity.skuNumber},
            #{entity.trayInfo}, #{entity.locationEnable}, #{entity.locationLock}, #{entity.locationUseStatus},
            #{entity.locationX}, #{entity.locationY}, #{entity.locationWide}, #{entity.locationDeep},
            #{entity.locationHeight}, #{entity.locationDefaultHeight}, #{entity.locationTotalHeight},
            #{entity.locationTrayHeight}, #{entity.locationStorey}, #{entity.locationType}, #{entity.locationNumber},
            #{entity.creator}, #{entity.createTime}, #{entity.updater}, #{entity.updateTime}, #{entity.deleted},
            #{entity.tenantId})
        </foreach>
    </insert>

    <insert id="insertOrUpdateBatch" keyProperty="id" useGeneratedKeys="true">
        insert into ware_house_location(lane_id, lane_name, area_id, area_name, location_no, location_yaw, group_name,
        sku_info, sku_batch, sku_number, tray_info, location_enable, location_lock, location_use_status, location_x,
        location_y, location_wide, location_deep, location_height, location_default_height, location_total_height,
        location_tray_height, location_storey, location_type, location_number, creator, create_time, updater,
        update_time, deleted, tenant_id)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.laneId}, #{entity.laneName}, #{entity.areaId}, #{entity.areaName}, #{entity.locationNo},
            #{entity.locationYaw}, #{entity.groupName}, #{entity.skuInfo}, #{entity.skuBatch}, #{entity.skuNumber},
            #{entity.trayInfo}, #{entity.locationEnable}, #{entity.locationLock}, #{entity.locationUseStatus},
            #{entity.locationX}, #{entity.locationY}, #{entity.locationWide}, #{entity.locationDeep},
            #{entity.locationHeight}, #{entity.locationDefaultHeight}, #{entity.locationTotalHeight},
            #{entity.locationTrayHeight}, #{entity.locationStorey}, #{entity.locationType}, #{entity.locationNumber},
            #{entity.creator}, #{entity.createTime}, #{entity.updater}, #{entity.updateTime}, #{entity.deleted},
            #{entity.tenantId})
        </foreach>
        on duplicate key update
        lane_id = values(lane_id),
        lane_name = values(lane_name),
        area_id = values(area_id),
        area_name = values(area_name),
        location_no = values(location_no),
        location_yaw = values(location_yaw),
        group_name = values(group_name),
        sku_info = values(sku_info),
        sku_batch = values(sku_batch),
        sku_number = values(sku_number),
        tray_info = values(tray_info),
        location_enable = values(location_enable),
        location_lock = values(location_lock),
        location_use_status = values(location_use_status),
        location_x = values(location_x),
        location_y = values(location_y),
        location_wide = values(location_wide),
        location_deep = values(location_deep),
        location_height = values(location_height),
        location_default_height = values(location_default_height),
        location_total_height = values(location_total_height),
        location_tray_height = values(location_tray_height),
        location_storey = values(location_storey),
        location_type = values(location_type),
        location_number = values(location_number),
        creator = values(creator),
        create_time = values(create_time),
        updater = values(updater),
        update_time = values(update_time),
        deleted = values(deleted),
        tenant_id = values(tenant_id)
    </insert>

    <!--通过主键修改数据-->
    <update id="updateEntity">
        update ware_house_location
        <set>
            <if test="laneId != null">
                lane_id = #{laneId},
            </if>
            <if test="laneName != null and laneName != ''">
                lane_name = #{laneName},
            </if>
            <if test="areaId != null">
                area_id = #{areaId},
            </if>
            <if test="areaName != null and areaName != ''">
                area_name = #{areaName},
            </if>
            <if test="locationNo != null and locationNo != ''">
                location_no = #{locationNo},
            </if>
            <if test="locationYaw != null and locationYaw != ''">
                location_yaw = #{locationYaw},
            </if>
            <if test="groupName != null and groupName != ''">
                group_name = #{groupName},
            </if>
                sku_info = #{skuInfo},
                sku_batch = #{skuBatch},
            <if test="skuNumber != null">
                sku_number = #{skuNumber},
            </if>
            <if test="trayInfo != null and trayInfo != ''">
                tray_info = #{trayInfo},
            </if>
            <if test="locationEnable != null">
                location_enable = #{locationEnable},
            </if>
            <if test="locationLock != null">
                location_lock = #{locationLock},
            </if>
            <if test="locationUseStatus != null">
                location_use_status = #{locationUseStatus},
            </if>
            <if test="locationX != null and locationX != ''">
                location_x = #{locationX},
            </if>
            <if test="locationY != null and locationY != ''">
                location_y = #{locationY},
            </if>
            <if test="locationWide != null">
                location_wide = #{locationWide},
            </if>
            <if test="locationDeep != null">
                location_deep = #{locationDeep},
            </if>
            <if test="locationHeight != null">
                location_height = #{locationHeight},
            </if>
            <if test="locationDefaultHeight != null">
                location_default_height = #{locationDefaultHeight},
            </if>
            <if test="locationTotalHeight != null">
                location_total_height = #{locationTotalHeight},
            </if>
            <if test="locationTrayHeight != null">
                location_tray_height = #{locationTrayHeight},
            </if>
            <if test="locationStorey != null">
                location_storey = #{locationStorey},
            </if>
            <if test="locationType != null">
                location_type = #{locationType},
            </if>
            <if test="locationNumber != null">
                location_number = #{locationNumber},
            </if>
            <if test="creator != null and creator != ''">
                creator = #{creator},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
            <if test="updater != null and updater != ''">
                updater = #{updater},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
            <if test="deleted != null">
                deleted = #{deleted},
            </if>
            <if test="tenantId != null">
                tenant_id = #{tenantId},
            </if>
        </set>
        where id = #{id}
    </update>



    <update id="updateLocationLockList">
        update
            ware_house_location
        set
            location_lock = #{locationLock},
            task_id = #{taskId}
        where
            id in
            <foreach collection="locationIds" item="id" index="index" open="(" close=")"
                           separator=",">
                #{id}
            </foreach>
    </update>
    <update id="releaseLocationLockList">
        update
            ware_house_location
        set
            location_lock = #{locationLock}
        where
            task_id = #{taskId}
            and location_lock = '0'
            and id in
            <foreach collection="locationIds" item="id" index="index" open="(" close=")"
                     separator=",">
                #{id}
            </foreach>
    </update>
    <update id="updateLocationLaneNameByLaneId">
        update
            ware_house_location
        set
            lane_name = #{laneName}
        where
            lane_id = #{laneId}
    </update>

    <update id="updateLocationLaneNameEmptyByLaneId">
        update
            ware_house_location
        set
            lane_name = null,
            lane_id = null
        where
            lane_id = #{laneId}
    </update>
    <update id="updateLocationAreaNameEmptyByAreaId">
        update
            ware_house_location
        set
            area_name = null,
            area_id = null,
            sku_info = null
        where
            area_id = #{areaId}
    </update>


    <select id="selectByTypeAndId" resultMap="BaseResultMap">
        select
        <include refid="base_sql"></include>
        from
            ware_house_location t1
        <where>
            t1.deleted = '0'
            <if test= " locationUseStatus != null ">
                AND t1.location_use_status = #{locationUseStatus}
            </if>
            <if test= "takeType != null and takeType == '1'.toString()">
                AND t1.id = #{id}
            </if>
            <if test= "takeType != null and takeType == '2'.toString()">
                AND t1.lane_id = #{id}
            </if>
            <if test= "takeType != null and takeType == '3'.toString()">
                AND t1.area_id = #{id}
            </if>
            <if test= " locationIds != null and locationIds.size() > 0">
                AND t1.id not in
                <foreach collection="locationIds" item="id" index="index" open="(" close=")"
                         separator=",">
                    #{id}
                </foreach>
            </if>
            <if test= " mapIds != null and mapIds.size() > 0">
                AND t1.map_id in
                <foreach collection="mapIds" item="mapId" index="index" open="(" close=")"
                         separator=",">
                    #{mapId}
                </foreach>
            </if>
            <if test= '"1"=="1"'>
                AND t1.location_enable = '1'
                AND t1.location_lock = '1'
                AND t1.location_type = '0'
            </if>
            <if test= "locationUseStatus != null and locationUseStatus == '0'.toString()">
                AND ((t1.lane_id > 0
                     and t1.location_number &lt; (SELECT IFNULL(min( t2.location_number ) ,10000000000) from ware_house_location t2
                        where t1.lane_id = t2.lane_id
                        AND t2.location_type = '0'
                        AND (t2.location_use_status = '1' or t2.location_enable = '0' or t2.location_lock = '0'))
                ) or t1.lane_id is null)
            </if>
            <if test= " takeMapItemIds != null and takeMapItemIds.size() > 0">
                AND t1.map_item_id not in
                <foreach collection="takeMapItemIds" item="mapItemId" index="index" open="(" close=")"
                         separator=",">
                    #{mapItemId}
                </foreach>
            </if>
            <if test= " laneIds != null and laneIds.size() > 0">
                AND t1.lane_id in
                <foreach collection="laneIds" item="laneId" index="index" open="(" close=")"
                         separator=",">
                    #{laneId}
                </foreach>
            </if>
            <if test= " areaIds != null and areaIds.size() > 0">
                AND t1.area_id in
                <foreach collection="areaIds" item="areaId" index="index" open="(" close=")"
                         separator=",">
                    #{areaId}
                </foreach>
            </if>
        </where>
        order by t1.location_number desc , t1.location_storey asc
        limit 1
    </select>

    <select id="selectLocations"
            resultType="cn.iocoder.yudao.module.system.dal.dataobject.houselocation.WareHouseLocationDO">
        select
            <include refid="base_sql"></include>
        from
            ware_house_location
        <where>
            <if test="query.id != null">
                and id = #{query.id}
            </if>
            <if test="query.laneId != null">
                and lane_id = #{query.laneId}
            </if>
            <if test="query.laneName != null and query.laneName != ''">
                and lane_name = #{query.laneName}
            </if>
            <if test="query.areaId != null">
                and area_id = #{query.areaId}
            </if>
            <if test="query.areaName != null and query.areaName != ''">
                and area_name = #{query.areaName}
            </if>
            <if test="query.locationNo != null and query.locationNo != ''">
                and location_no = #{query.locationNo}
            </if>
            <if test="query.locationYaw != null and query.locationYaw != ''">
                and location_yaw = #{query.locationYaw}
            </if>
            <if test="query.groupName != null and query.groupName != ''">
                and group_name = #{query.groupName}
            </if>
            <if test="query.skuInfo != null and query.skuInfo != ''">
                and sku_info = #{query.skuInfo}
            </if>
            <if test="query.skuBatch != null and query.skuBatch != ''">
                and sku_batch = #{query.skuBatch}
            </if>
            <if test="query.skuNumber != null">
                and sku_number = #{query.skuNumber}
            </if>
            <if test="query.trayInfo != null and query.trayInfo != ''">
                and tray_info = #{query.trayInfo}
            </if>
            <if test="query.locationEnable != null">
                and location_enable = #{query.locationEnable}
            </if>
            <if test="query.locationLock != null">
                and location_lock = #{query.locationLock}
            </if>
            <if test="query.locationUseStatus != null">
                and location_use_status = #{query.locationUseStatus}
            </if>
            <if test="query.locationX != null and query.locationX != ''">
                and location_x = #{query.locationX}
            </if>
            <if test="query.locationY != null and query.locationY != ''">
                and location_y = #{query.locationY}
            </if>
            <if test="query.locationWide != null">
                and location_wide = #{query.locationWide}
            </if>
            <if test="query.locationDeep != null">
                and location_deep = #{query.locationDeep}
            </if>
            <if test="query.locationHeight != null">
                and location_height = #{query.locationHeight}
            </if>
            <if test="query.locationDefaultHeight != null">
                and location_default_height = #{query.locationDefaultHeight}
            </if>
            <if test="query.locationTotalHeight != null">
                and location_total_height = #{query.locationTotalHeight}
            </if>
            <if test="query.locationTrayHeight != null">
                and location_tray_height = #{query.locationTrayHeight}
            </if>
            <if test="query.locationStorey != null">
                and location_storey = #{query.locationStorey}
            </if>
            <if test="query.locationType != null">
                and location_type = #{query.locationType}
            </if>
            <if test="query.locationNumber != null">
                and location_number = #{query.locationNumber}
            </if>
            <if test="1==1">
                and deleted = '0'
            </if>
            <if test="locationIds != null and locationIds.size() > 0">
                and id not in
                <foreach collection="locationIds" item="item" index="index" open="(" close=")"
                         separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="mapIds != null and mapIds.size() > 0">
                and map_id in
                <foreach collection="mapIds" item="mapId" index="index" open="(" close=")"
                         separator=",">
                    #{mapId}
                </foreach>
            </if>
            <if test="laneIds != null and laneIds.size() > 0">
                and lane_id in
                <foreach collection="laneIds" item="item" index="index" open="(" close=")"
                         separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="areaIds != null and areaIds.size() > 0">
                and area_id in
                <foreach collection="areaIds" item="item" index="index" open="(" close=")"
                         separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="mapItemIds != null and mapItemIds.size() > 0">
                and map_item_id not in
                <foreach collection="mapItemIds" item="item" index="index" open="(" close=")"
                         separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
        order by location_number asc
    </select>
    <select id="getLocationByName" resultType="cn.iocoder.yudao.module.system.controller.admin.houselocation.vo.WareHouseLocationRespVO">
        <choose>
            <when test="type == 1">
                select
                    id,
                    location_no as locationNo
                from
                    ware_house_location
                where
                    location_no like concat('%', #{locationNo}, '%')
                and deleted = '0'
            </when>
            <when test="type == 2">
                select
                    id as id,
                    lane_name as locationNo
                from
                    ware_house_lane
                where
                    lane_name like concat('%', #{locationNo}, '%')
                and deleted = '0'
            </when>
            <when test="type == 3">
                select
                    id as id,
                    area_name as locationNo
                from
                    ware_house_area
                where
                    area_name like concat('%', #{locationNo}, '%')
                and deleted = '0'
            </when>
            <when test="type == 4">
                select
                id as id,
                sort_num as locationNo
                from
                ware_position_map_item
                where
                    sort_num like concat('%', #{locationNo}, '%')
                and deleted = '0'
                and type != '2'
            </when>
        </choose>
    </select>


    <select id="selectLocationByList"  resultMap="BaseResultMap">
        select
            <include refid="base_sql"></include>
        from
        ware_house_location
            <where>
                <foreach collection="locations" item="item" open="(" close=")"
                         separator=",">
                    1=1
                    <if test="item.laneId != null">
                        and lane_id = #{item.laneId}
                    </if>
                    <if test="item.locationNumber != null">
                        and location_number &lt; #{item.locationNumber}
                    </if>
                    <if test="item.locationLock != null">
                        and location_lock = #{item.locationLock}
                    </if>
                    <if test="item.locationUseStatus != null">
                        and location_use_status = #{item.locationUseStatus}
                    </if>
                    <if test="1==1">
                        and deleted = '0'
                    </if>
                </foreach>
        </where>
        order by  location_number asc
    </select>


    <select id="selectNeedMoveLocation" resultMap="BaseResultMap">
        select
            t2.id,
            t2.lane_id,
            t2.lane_name,
            t2.area_id,
            t2.area_name,
            t2.location_no,
            t2.location_yaw,
            t2.group_name,
            t2.sku_info,
            t2.sku_batch,
            t2.sku_number,
            t2.tray_info,
            t2.location_enable,
            t2.location_lock,
            t2.location_use_status,
            t2.location_x,
            t2.location_y,
            t2.location_wide,
            t2.location_deep,
            t2.location_height,
            t2.location_default_height,
            t2.location_total_height,
            t2.location_tray_height,
            t2.location_storey,
            t2.location_type,
            t2.location_number,
            t2.map_id,
            t2.map_item_id
        from
            ware_house_location t1, ware_house_location t2
        WHERE
        t1.map_item_id = t2.map_item_id
        and t1.id in
        <foreach collection="locationIds" item="id" index="index" open="(" close=")"
                 separator=",">
            #{id}
        </foreach>
        and t2.location_number &lt; t1.location_number
        and t2.location_use_status = '1'
        and t1.deleted = '0'
        and t2.deleted = '0'
        and t2.id not in
        <foreach collection="locationIds" item="id" index="index" open="(" close=")"
                 separator=",">
            #{id}
        </foreach>
        order by t2.location_number asc
        limit 1
    </select>


    <select id="queryUpperLevelsByMapItemId" resultType="Long">
        select
            id
        from
            ware_house_location
        <where>
            deleted = '0'
            and map_item_id = #{mapItemId}
            and location_storey &gt; #{locationStorey}
            and location_use_status = '1'
            and location_lock = '1'
        </where>
    </select>

    <select id="queryLowerLevelsByMapItemId"
            resultType="Long">
        select
            id
        from
            ware_house_location
        <where>
            deleted = '0'
            and map_item_id = #{mapItemId}
            and location_storey &lt; #{locationStorey}
            and location_use_status = '0'
            and location_lock = '1'
        </where>
    </select>

    <select id="getLocationsXYByMapId"
            resultType="cn.iocoder.yudao.module.system.controller.admin.houselocation.vo.WareHouseLocationItemRespVO">
        SELECT
            t1.id,
            t1.location_no as locationNo,
            t1.sku_info as skuInfo,
            t1.sku_number as skuNumber,
            t1.location_enable as locationEnable,
            t1.location_lock as locationLock,
            t1.location_use_status as locationUseStatus,
            t1.location_wide as locationWide,
            t1.location_deep as locationDeep,
            t1.location_storey as locationStorey,
            t2.actual_location_x as actualLocationX,
            t2.actual_location_y as actualLocationY
        FROM
            ware_house_location t1
                LEFT JOIN ware_position_map_item t2
                          on t1.map_item_id = t2.id
        WHERE
            t1.deleted = '0'
          AND t2.deleted = '0'
          AND t1.map_id = #{mapId}
    </select>
    <select id="getInStockLockLocationByMapId"
            resultType="cn.iocoder.yudao.module.system.dal.dataobject.houselocation.WareHouseLocationDO">
        select
            *
        from
            ware_house_location
        where
            deleted = '0'
        and map_id = #{mapId}
        and (location_lock = '0' or location_use_status = '1')
    </select>

</mapper>
