<?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="com.dgtly.system.mapper.MetaHanaDeliverSignMapper">

    <resultMap type="MetaHanaDeliverSign" id="MetaHanaDeliverSignResult">
        <result property="deliverNumber"    column="deliver_number"    />
        <result property="deliverItem"    column="deliver_Item"    />
        <result property="matnrCode"    column="matnr_code"    />
        <result property="orderNumber"    column="order_number"    />
        <result property="orderItem"    column="order_Item"    />
        <result property="orderCreateTime"    column="order_create_time"    />
        <result property="deliverQty"    column="deliver_qty"    />
        <result property="actualQty"    column="actual_qty"    />
        <result property="differenceQty"    column="difference_qty"    />
        <result property="zdateDate"    column="zdate_date"    />
        <result property="zdateTime"    column="zdate_time"    />
        <result property="abnormalOrder"    column="abnormal_order"    />
        <result property="abnormalStatus"    column="abnormal_status"    />
    </resultMap>

    <sql id="selectMetaHanaDeliverSignVo">
        select deliver_number, deliver_Item,matnr_code, order_number, order_Item, order_create_time, deliver_qty, actual_qty, difference_qty, zdate_date, zdate_time, abnormal_order, abnormal_status from meta_hana_deliver_sign
    </sql>

    <select id="selectMetaHanaDeliverSignList" parameterType="MetaHanaDeliverSign" resultMap="MetaHanaDeliverSignResult">
        <include refid="selectMetaHanaDeliverSignVo"/>
        <where>
            <if test="deliverNumber != null  and deliverNumber != ''"> and deliver_number = #{deliverNumber}</if>
            <if test="deliverItem != null  and deliverItem != ''"> and deliver_Item = #{deliverItem}</if>
            <if test="matnrCode != null  and matnrCode != ''"> and matnr_code = #{matnrCode}</if>
            <if test="orderNumber != null  and orderNumber != ''"> and order_number = #{orderNumber}</if>
            <if test="orderItem != null  and orderItem != ''"> and order_Item = #{orderItem}</if>
            <if test="orderCreateTime != null  and orderCreateTime != ''"> and order_create_time = #{orderCreateTime}</if>
            <if test="deliverQty != null  and deliverQty != ''"> and deliver_qty = #{deliverQty}</if>
            <if test="actualQty != null  and actualQty != ''"> and actual_qty = #{actualQty}</if>
            <if test="differenceQty != null  and differenceQty != ''"> and difference_qty = #{differenceQty}</if>
            <if test="zdateDate != null  and zdateDate != ''"> and zdate_date = #{zdateDate}</if>
            <if test="zdateTime != null  and zdateTime != ''"> and zdate_time = #{zdateTime}</if>
            <if test="abnormalOrder != null  and abnormalOrder != ''"> and abnormal_order = #{abnormalOrder}</if>
            <if test="abnormalStatus != null  and abnormalStatus != ''"> and abnormal_status = #{abnormalStatus}</if>

        </where>
    </select>

    <select id="selectMetaHanaDeliverSignById" parameterType="String" resultMap="MetaHanaDeliverSignResult">
        <include refid="selectMetaHanaDeliverSignVo"/>
        where deliver_number = #{deliverNumber}
    </select>



    <update id="updateMetaHanaDeliverSign" parameterType="MetaHanaDeliverSign">
        update meta_hana_deliver_sign
        <trim prefix="SET" suffixOverrides=",">
            <if test="matnrCode != null  and matnrCode != ''">matnr_code = #{matnrCode},</if>
            <if test="orderNumber != null  and orderNumber != ''">order_number = #{orderNumber},</if>
            <if test="orderItem != null  and orderItem != ''">order_Item = #{orderItem},</if>
            <if test="orderCreateTime != null  and orderCreateTime != ''">order_create_time = #{orderCreateTime},</if>
            <if test="deliverQty != null  and deliverQty != ''">deliver_qty = #{deliverQty},</if>
            <if test="actualQty != null  and actualQty != ''">actual_qty = #{actualQty},</if>
            <if test="differenceQty != null  and differenceQty != ''">difference_qty = #{differenceQty},</if>
            <if test="zdateDate != null  and zdateDate != ''">zdate_date = #{zdateDate},</if>
            <if test="zdateTime != null  and zdateTime != ''">zdate_time = #{zdateTime},</if>
            <if test="abnormalOrder != null  and abnormalOrder != ''">abnormal_order = #{abnormalOrder},</if>
            <if test="abnormalStatus != null  and abnormalStatus != ''">abnormal_status = #{abnormalStatus},</if>

        </trim>
        where deliver_number = #{deliverNumber} and deliver_Item =#{deliverItem}
    </update>

    <delete id="deleteMetaHanaDeliverSignById" parameterType="String">
        delete from meta_hana_deliver_sign where deliver_number = #{deliverNumber}
    </delete>

    <delete id="deleteMetaHanaDeliverSignByIds" parameterType="String">
        delete from meta_hana_deliver_sign where deliver_number in
        <foreach item="deliverNumber" collection="array" open="(" separator="," close=")">
            #{deliverNumber}
        </foreach>
    </delete>

    <select id="selectMetaHanaDeliverSignListHavingAbnormal" parameterType="MetaHanaDeliverSign" resultMap="MetaHanaDeliverSignResult">
        <include refid="selectMetaHanaDeliverSignVo"/>
        WHERE abnormal_order IS NOT NULL and abnormal_order !='' and abnormal_order !='null'
    </select>

    <select id="selectAbnormalSignByabnormalOrderAndItem" parameterType="MetaHanaDeliverSign" resultMap="MetaHanaDeliverSignResult">
        <include refid="selectMetaHanaDeliverSignVo"/>
         <where>
            <if test="abnormalOrder != null  and abnormalOrder != ''"> and order_number = #{abnormalOrder}</if>
         </where>
         limit 1
    </select>

    <select id="selectDeliverSignByOrderNumber" resultType="com.dgtly.system.domain.DeliverSignVO">
        SELECT
            abnormal_order AS abnormalOrder,
            SUM( difference_qty ) AS differenceQty,
            min( CASE WHEN abnormal_status = '未开始补货' THEN 1 WHEN abnormal_status = '补货中' THEN 2 WHEN abnormal_status = '部分签收' THEN 3 WHEN abnormal_status = '全部签收' THEN 4 ELSE 5 END ) AS abnormalStatus,
            max(
                    DATE_FORMAT( STR_TO_DATE( CONCAT( zdate_date, zdate_time ), '%Y%m%d%H%i%s' ), '%Y-%m-%d %H:%i:%s ' )) AS zdateDate
        FROM
            meta_hana_deliver_sign
        WHERE
            order_number = #{OrderNumber}
          AND abnormal_order IS NOT NULL
          AND abnormal_order != ''
        group by abnormal_order
    </select>

    <select id="selectDeliverSignListByOrderNumberAndOrderItem" resultType="com.dgtly.system.domain.MetaHanaDeliverSign">
        SELECT
            a.order_number AS orderNumber,
            a.difference_qty AS differenceQty,
            a.zdate_date AS zdateDate,
            a.abnormal_status AS abnormalStatus,
            DATE_FORMAT( MAX( STR_TO_DATE( CONCAT( a.zdate_date, a.zdate_time ), '%Y%m%d%H%i%s' )), '%Y-%m-%d %H:%i:%s' ) AS signTime
        FROM
            meta_hana_deliver_sign a
        GROUP BY
            a.order_number,
            a.difference_qty,
            a.zdate_date,
            a.zdate_time,
            a.abnormal_status
    </select>



    <select id="selectDeliverSignByTmsShipmentNumber" resultType="com.dgtly.system.domain.DeliverSignVO">
        SELECT
            b.tms_shipment_number AS tmsShipmentNum,
            a.difference_qty AS differenceQty,
            CASE
                a.abnormal_status
                WHEN '补货中' THEN
                    1
                WHEN '未开始补货' THEN
                    2
                WHEN '部分签收' THEN
                    3
                WHEN '全部签收' THEN
                    4 ELSE 5
                END AS abnormalStatus,
            DATE_FORMAT( MAX( STR_TO_DATE( CONCAT( a.zdate_date, a.zdate_time ), '%Y%m%d%H%i%s' )), '%Y-%m-%d %H:%i:%s' ) AS zdateDate,
            a.zdate_date AS zDate
        FROM
            meta_hana_deliver_sign a
                JOIN (
                SELECT
                    tms_shipment_number,
                    delivery_number
                FROM
                    tms_logistics_status
                WHERE
                    is_delete = '0'
                  AND date_format( create_time, '%Y%m%d' ) >= DATE_ADD( LAST_DAY( NOW() - INTERVAL 4 MONTH ), INTERVAL 1 DAY )
                GROUP BY
                    tms_shipment_number,
                    delivery_number
            ) b ON a.deliver_number = b.delivery_number
        GROUP BY
            a.deliver_number,
            a.deliver_Item,
            a.difference_qty,
            a.abnormal_status,
            a.zdate_date,
            a.zdate_time

    </select>

    <select id="selectOrderNumberSign" resultType="java.lang.String">
        SELECT distinct order_number FROM meta_hana_deliver_sign
    </select>

    <select id="selectDeliverNumListByTmsShipmentNumber" resultType="java.lang.String">
        SELECT DISTINCT delivery_number FROM tms_logistics_status WHERE is_delete = '0' AND tms_shipment_number =#{tmsShipmentNumber}
    </select>

    <select id="selectTmsShipmentNumberSign" resultType="java.lang.String">
        SELECT DISTINCT
            tms_shipment_number
        FROM
            tms_logistics_status
        WHERE
            is_delete = '0'
          AND date_format( create_time, '%Y%m%d' ) >= DATE_ADD( LAST_DAY( NOW() - INTERVAL 4 MONTH ), INTERVAL 1 DAY )
    </select>

    <update id="truncateOrderSign">
        TRUNCATE TABLE pretreatment_hana_order_sign
    </update>

    <update id="truncateTmsShipmentSign">
        TRUNCATE TABLE pretreatment_hana_tms_shipment_sign
    </update>

    <insert id="batchOrderSign">
        insert into pretreatment_hana_order_sign
        (order_number,sign_time,sign_type)
        values
        <foreach collection="list" item="sign" separator=",">
            (#{sign.orderNumber},#{sign.zdateDate},
            #{sign.signType})
        </foreach>
    </insert>

    <insert id="batchTmsShipmentSign">
        insert into pretreatment_hana_tms_shipment_sign
        (tms_shipment_number,sign_time,sign_type)
        values
        <foreach collection="list" item="sign" separator=",">
            (#{sign.tmsShipmentNumber},#{sign.zdateDate},
            #{sign.signType})
        </foreach>
    </insert>


</mapper>
