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

  <select id="getBomList" parameterType="map" resultType="map">
    SELECT
      B.ID, B.COMPANY_CODE, B.ITEM_ID, B.ITEM_CODE,
      COALESCE(B.ITEM_NAME, II.ITEM_NAME) AS ITEM_NAME,
      B.BASE_QTY, B.UNIT, B.VERSION, B.STATUS, B.REMARK,
      B.WRITER, B.CURRENT_VERSION_ID, B.CREATED_DATE, B.UPDATED_DATE,
      II.ITEM_NUMBER, II.DIVISION AS ITEM_TYPE
    FROM BOM B
    LEFT JOIN ITEM_INFO II ON B.ITEM_ID = II.ID AND B.COMPANY_CODE = II.COMPANY_CODE
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <if test="item_code != null and item_code != ''">
      AND B.ITEM_CODE ILIKE '%' || #{item_code} || '%'
    </if>
    <if test="item_name != null and item_name != ''">
      AND COALESCE(B.ITEM_NAME, II.ITEM_NAME) ILIKE '%' || #{item_name} || '%'
    </if>
    <if test="status != null and status != ''">
      AND B.STATUS = #{status}
    </if>
    ORDER BY B.CREATED_DATE DESC
    <include refid="common.pagination"/>
  </select>

  <select id="getBomListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM BOM B
    LEFT JOIN ITEM_INFO II ON B.ITEM_ID = II.ID AND B.COMPANY_CODE = II.COMPANY_CODE
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <if test="item_code != null and item_code != ''">
      AND B.ITEM_CODE ILIKE '%' || #{item_code} || '%'
    </if>
    <if test="item_name != null and item_name != ''">
      AND COALESCE(B.ITEM_NAME, II.ITEM_NAME) ILIKE '%' || #{item_name} || '%'
    </if>
    <if test="status != null and status != ''">
      AND B.STATUS = #{status}
    </if>
  </select>

  <select id="getBomInfo" parameterType="map" resultType="map">
    SELECT
      B.ID, B.COMPANY_CODE, B.ITEM_ID, B.ITEM_CODE,
      COALESCE(B.ITEM_NAME, II.ITEM_NAME) AS ITEM_NAME,
      B.BASE_QTY, B.UNIT, B.VERSION, B.STATUS, B.REMARK,
      B.WRITER, B.CURRENT_VERSION_ID, B.CREATED_DATE, B.UPDATED_DATE,
      II.ITEM_NUMBER, II.DIVISION AS ITEM_TYPE, II.UNIT AS ITEM_UNIT,
      II.DIVISION, II.SIZE, II.MATERIAL
    FROM BOM B
    LEFT JOIN ITEM_INFO II ON B.ITEM_ID = II.ID AND B.COMPANY_CODE = II.COMPANY_CODE
    WHERE B.ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </select>

  <insert id="insertBom" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO BOM (
      COMPANY_CODE, ITEM_ID, ITEM_CODE, ITEM_NAME, BASE_QTY, UNIT, VERSION, STATUS, REMARK, WRITER
      , CREATED_DATE, UPDATED_DATE
    ) VALUES (
      #{company_code}, #{item_id}, #{item_code}, #{item_name}, #{base_qty}, #{unit},
      COALESCE(#{version}, '1.0'), COALESCE(#{status}, 'active'), #{remark}, #{writer}
      , NOW(), NOW()
    )
  </insert>

  <update id="updateBom" parameterType="map">
    UPDATE BOM
    <set>
      <if test="item_code != null">
        ITEM_CODE = #{item_code},
      </if>
      <if test="item_name != null">
        ITEM_NAME = #{item_name},
      </if>
      <if test="base_qty != null">
        BASE_QTY = #{base_qty},
      </if>
      <if test="unit != null">
        UNIT = #{unit},
      </if>
      <if test="version != null">
        VERSION = #{version},
      </if>
      <if test="status != null">
        STATUS = #{status},
      </if>
      <if test="remark != null">
        REMARK = #{remark},
      </if>
      UPDATED_DATE = NOW()
    </set>
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </update>

  <delete id="deleteBom" parameterType="map">
    DELETE FROM BOM
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </delete>

</mapper>
