<?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="packaging">

  <!-- ──────────────────────────────────────────────────────────
       포장단위 (pkg_unit)
  ────────────────────────────────────────────────────────── -->

  <select id="getPkgUnits" parameterType="map" resultType="map">
    SELECT *
    FROM pkg_unit
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
    ORDER BY
    <choose>
      <when test="company_code != null and company_code == &quot;*&quot;">company_code, created_date DESC</when>
      <otherwise>created_date DESC</otherwise>
    </choose>
  </select>

  <insert id="insertPkgUnit" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO pkg_unit
      (company_code, pkg_code, pkg_name, pkg_type, status,
       width_mm, length_mm, height_mm, self_weight_kg, max_load_kg, volume_l, remarks, writer)
    VALUES
      (#{company_code}, #{pkg_code}, #{pkg_name}, #{pkg_type},
       COALESCE(#{status}, 'ACTIVE'),
       #{width_mm}, #{length_mm}, #{height_mm}, #{self_weight_kg}, #{max_load_kg}, #{volume_l},
       #{remarks}, #{writer})
  </insert>

  <update id="updatePkgUnit" parameterType="map">
    UPDATE pkg_unit SET
      pkg_name       = #{pkg_name},
      pkg_type       = #{pkg_type},
      status         = #{status},
      width_mm       = #{width_mm},
      length_mm      = #{length_mm},
      height_mm      = #{height_mm},
      self_weight_kg = #{self_weight_kg},
      max_load_kg    = #{max_load_kg},
      volume_l       = #{volume_l},
      remarks        = #{remarks},
      updated_date   = NOW(),
      writer         = #{writer}
    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </update>

  <delete id="deletePkgUnitItemsByUnitId" parameterType="map">
    DELETE FROM pkg_unit_item
    WHERE pkg_code = (
      SELECT pkg_code
      FROM pkg_unit
      WHERE id = #{id}
        AND (company_code = #{company_code} OR company_code = '*')
    )
    AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <delete id="deletePkgUnit" parameterType="map">
    DELETE FROM pkg_unit
    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <!-- ──────────────────────────────────────────────────────────
       포장단위 매칭품목 (pkg_unit_item)
  ────────────────────────────────────────────────────────── -->

  <select id="getPkgUnitItems" parameterType="map" resultType="map">
    SELECT *
    FROM pkg_unit_item
    WHERE pkg_code = #{pkg_code}
      AND (company_code = #{company_code} OR company_code = '*')
    ORDER BY created_date DESC
  </select>

  <insert id="insertPkgUnitItem" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO pkg_unit_item (company_code, pkg_code, item_number, pkg_qty, writer)
    VALUES (#{company_code}, #{pkg_code}, #{item_number}, #{pkg_qty}, #{writer})
  </insert>

  <delete id="deletePkgUnitItem" parameterType="map">
    DELETE FROM pkg_unit_item
    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <!-- ──────────────────────────────────────────────────────────
       적재함 (loading_unit)
  ────────────────────────────────────────────────────────── -->

  <select id="getLoadingUnits" parameterType="map" resultType="map">
    SELECT *
    FROM loading_unit
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
    ORDER BY
    <choose>
      <when test="company_code != null and company_code == &quot;*&quot;">company_code, created_date DESC</when>
      <otherwise>created_date DESC</otherwise>
    </choose>
  </select>

  <insert id="insertLoadingUnit" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO loading_unit
      (company_code, loading_code, loading_name, loading_type, status,
       width_mm, length_mm, height_mm, self_weight_kg, max_load_kg, max_stack, remarks, writer)
    VALUES
      (#{company_code}, #{loading_code}, #{loading_name}, #{loading_type},
       COALESCE(#{status}, 'ACTIVE'),
       #{width_mm}, #{length_mm}, #{height_mm}, #{self_weight_kg}, #{max_load_kg}, #{max_stack},
       #{remarks}, #{writer})
  </insert>

  <update id="updateLoadingUnit" parameterType="map">
    UPDATE loading_unit SET
      loading_name   = #{loading_name},
      loading_type   = #{loading_type},
      status         = #{status},
      width_mm       = #{width_mm},
      length_mm      = #{length_mm},
      height_mm      = #{height_mm},
      self_weight_kg = #{self_weight_kg},
      max_load_kg    = #{max_load_kg},
      max_stack      = #{max_stack},
      remarks        = #{remarks},
      updated_date   = NOW(),
      writer         = #{writer}
    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </update>

  <delete id="deleteLoadingUnitPkgsByUnitId" parameterType="map">
    DELETE FROM loading_unit_pkg
    WHERE loading_code = (
      SELECT loading_code
      FROM loading_unit
      WHERE id = #{id}
        AND (company_code = #{company_code} OR company_code = '*')
    )
    AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <delete id="deleteLoadingUnit" parameterType="map">
    DELETE FROM loading_unit
    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <!-- ──────────────────────────────────────────────────────────
       적재함 포장구성 (loading_unit_pkg)
  ────────────────────────────────────────────────────────── -->

  <select id="getLoadingUnitPkgs" parameterType="map" resultType="map">
    SELECT *
    FROM loading_unit_pkg
    WHERE loading_code = #{loading_code}
      AND (company_code = #{company_code} OR company_code = '*')
    ORDER BY created_date DESC
  </select>

  <insert id="insertLoadingUnitPkg" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO loading_unit_pkg (company_code, loading_code, pkg_code, max_load_qty, load_method, writer)
    VALUES (#{company_code}, #{loading_code}, #{pkg_code}, #{max_load_qty}, #{load_method}, #{writer})
  </insert>

  <delete id="deleteLoadingUnitPkg" parameterType="map">
    DELETE FROM loading_unit_pkg
    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

</mapper>
