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

  <sql id="shippingPlanJoins">
    LEFT JOIN sales_order_detail d
      ON sp.detail_id = d.id AND sp.company_code = d.company_code
    LEFT JOIN sales_order_mng m
      ON sp.sales_order_id = m.id AND sp.company_code = m.company_code
    LEFT JOIN LATERAL (
      SELECT item_name FROM item_info
      WHERE item_number = COALESCE(d.part_code, m.part_code)
        AND company_code = sp.company_code
      LIMIT 1
    ) i ON TRUE
    LEFT JOIN customer_mng c
      ON COALESCE(m.partner_id, d.delivery_partner_code) = c.customer_code
      AND sp.company_code = c.company_code
  </sql>

  <sql id="shippingPlanSearchCondition">
    <if test='company_code != null and company_code != "*"'>
      AND (sp.company_code = #{company_code} OR sp.company_code = '*')
    </if>
    <if test="date_from != null and date_from != ''">
      AND sp.plan_date &gt;= #{date_from}::date
    </if>
    <if test="date_to != null and date_to != ''">
      AND sp.plan_date &lt;= #{date_to}::date
    </if>
    <if test="status != null and status != ''">
      AND sp.status = #{status}
    </if>
    <if test="customer != null and customer != ''">
      AND (c.customer_name ILIKE '%' || #{customer} || '%'
        OR COALESCE(m.partner_id, d.delivery_partner_code, '') ILIKE '%' || #{customer} || '%')
    </if>
    <if test="keyword != null and keyword != ''">
      AND (
        COALESCE(m.order_no, d.order_no, '') ILIKE '%' || #{keyword} || '%'
        OR COALESCE(d.part_code, m.part_code, '') ILIKE '%' || #{keyword} || '%'
        OR COALESCE(i.item_name, d.part_name, m.part_name, '') ILIKE '%' || #{keyword} || '%'
        OR sp.shipment_plan_no ILIKE '%' || #{keyword} || '%'
      )
    </if>
  </sql>

  <select id="getShippingPlanList" parameterType="map" resultType="map">
    SELECT
      sp.id,
      sp.plan_date,
      sp.plan_qty,
      sp.status,
      sp.memo,
      sp.shipment_plan_no,
      sp.created_date,
      sp.created_by,
      sp.detail_id,
      sp.sales_order_id,
      sp.remain_qty,
      COALESCE(m.order_no, d.order_no, '') AS order_no,
      COALESCE(d.part_code, m.part_code, '') AS part_code,
      COALESCE(i.item_name, d.part_name, m.part_name, COALESCE(d.part_code, m.part_code, '')) AS part_name,
      COALESCE(d.spec, m.spec, '') AS spec,
      COALESCE(m.material, '') AS material,
      COALESCE(c.customer_name, m.partner_id, d.delivery_partner_code, '') AS customer_name,
      COALESCE(m.partner_id, d.delivery_partner_code, '') AS partner_code,
      COALESCE(d.due_date, m.due_date::text, '') AS due_date,
      COALESCE(NULLIF(d.qty,''), m.order_qty, '0') AS order_qty,
      COALESCE(NULLIF(d.ship_qty,''), m.ship_qty, '0') AS shipped_qty

    FROM shipment_plan sp
    <include refid="shippingPlanJoins"/>

    WHERE 1=1
    <include refid="shippingPlanSearchCondition"/>

    ORDER BY sp.created_date DESC
    <include refid="common.pagination"/>
  </select>

  <select id="getShippingPlanListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)

    FROM shipment_plan sp
    <include refid="shippingPlanJoins"/>

    WHERE 1=1
    <include refid="shippingPlanSearchCondition"/>
  </select>

  <select id="getShippingPlanInfo" parameterType="map" resultType="map">
    SELECT
      sp.*,
      COALESCE(m.order_no, d.order_no, '') AS order_no,
      COALESCE(d.part_code, m.part_code, '') AS part_code,
      COALESCE(i.item_name, d.part_name, m.part_name, '') AS part_name,
      COALESCE(c.customer_name, m.partner_id, d.delivery_partner_code, '') AS customer_name

    FROM shipment_plan sp
    <include refid="shippingPlanJoins"/>

    WHERE sp.id = #{id}
    <if test='company_code != null and company_code != "*"'>
      AND (sp.company_code = #{company_code} OR sp.company_code = '*')
    </if>
  </select>

  <insert id="insertShippingPlan" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO shipment_plan (
      company_code, detail_id, sales_order_id,
      plan_qty, plan_date, status, memo, created_by, created_date
    ) VALUES (
      #{company_code},
      #{detail_id},
      #{sales_order_id}::integer,
      #{plan_qty}::numeric,
      COALESCE(#{plan_date}::date, CURRENT_DATE),
      COALESCE(#{status}, 'READY'),
      #{memo},
      #{created_by},
      NOW()
    )
  </insert>

  <update id="updateShippingPlan" parameterType="map">
    UPDATE shipment_plan

    SET
      updated_date = NOW()
      <if test="updated_by != null">, updated_by = #{updated_by}</if>
      <if test="plan_qty != null">, plan_qty = #{plan_qty}::numeric</if>
      <if test="plan_date != null and plan_date != ''">, plan_date = #{plan_date}::date</if>
      <if test="memo != null">, memo = #{memo}</if>
      <if test="status != null">, status = #{status}</if>

    WHERE id = #{id}
    <if test='company_code != null and company_code != "*"'>
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </update>

  <delete id="deleteShippingPlan" parameterType="map">
    DELETE FROM shipment_plan

    WHERE id = #{id}
    <if test='company_code != null and company_code != "*"'>
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </delete>

</mapper>
