<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.mes.warehouse.mapper.WarehouseMapper">

  <resultMap id="whMap" type="com.zioinfo.mes.warehouse.MesWarehouse">
    <id property="id" column="id"/>
    <result property="warehouseCode" column="warehouse_code"/>
    <result property="warehouseName" column="warehouse_name"/>
    <result property="warehouseType" column="warehouse_type"/>
    <result property="address" column="address"/>
    <result property="status" column="status"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <resultMap id="locMap" type="com.zioinfo.mes.warehouse.MesLocation">
    <id property="id" column="id"/>
    <result property="warehouseCode" column="warehouse_code"/>
    <result property="locationCode" column="location_code"/>
    <result property="zone" column="zone"/>
    <result property="aisle" column="aisle"/>
    <result property="rack" column="rack"/>
    <result property="bin" column="bin"/>
    <result property="status" column="status"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="whMap">
    SELECT * FROM mes_warehouse
    <where><if test="status != null and status != ''">AND status = #{status}</if></where>
    ORDER BY id ASC
  </select>
  <select id="findById" resultMap="whMap">SELECT * FROM mes_warehouse WHERE id = #{id}</select>
  <select id="countByCode" resultType="int">
    SELECT COUNT(*) FROM mes_warehouse WHERE warehouse_code = #{warehouseCode}
    <if test="excludeId != null">AND id != #{excludeId}</if>
  </select>

  <insert id="insert" parameterType="com.zioinfo.mes.warehouse.MesWarehouse" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_warehouse (warehouse_code, warehouse_name, warehouse_type, address, status, created_by)
    VALUES (#{warehouseCode}, #{warehouseName}, #{warehouseType}, #{address}, #{status}, #{createdBy})
  </insert>
  <update id="update" parameterType="com.zioinfo.mes.warehouse.MesWarehouse">
    UPDATE mes_warehouse SET warehouse_code = #{warehouseCode}, warehouse_name = #{warehouseName},
      warehouse_type = #{warehouseType}, address = #{address}, status = #{status}
    WHERE id = #{id}
  </update>
  <delete id="delete">DELETE FROM mes_warehouse WHERE id = #{id}</delete>

  <select id="findLocations" resultMap="locMap">
    SELECT * FROM mes_location WHERE warehouse_code = #{warehouseCode} ORDER BY location_code ASC
  </select>
  <insert id="insertLocation" parameterType="com.zioinfo.mes.warehouse.MesLocation" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_location (warehouse_code, location_code, zone, aisle, rack, bin, status)
    VALUES (#{warehouseCode}, #{locationCode}, #{zone}, #{aisle}, #{rack}, #{bin}, #{status})
  </insert>
  <delete id="deleteLocation">DELETE FROM mes_location WHERE id = #{id}</delete>

</mapper>
