package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmswms.entity.SapBoxNote;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-03-29
 */
@Mapper
public interface SapBoxNoteMapper extends BaseMapper<SapBoxNote> {

    @Select("SELECT BN.BOX_NOTE AS \"boxNote\",\n" +
            "       BND.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       BND.ITEM AS \"item\",\n"+
            "       BND.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
            "       BND.MATERIAL_NO AS \"materialNo\",\n" +
            "       BND.BASIC_UNIT AS \"basicUnit\",\n" +
            "       BND.SPECS AS \"specs\",\n" +
            "       BND.QUANTITY AS \"qty\",\n" +
            "       BND.SRM_CONTRACT_NO AS \"srmContractNo\",\n" +
            "       BND.PURCHASE_NO AS \"purchaseNo\",\n" +
            "       BND.DEMAND_NO AS \"demandNo\",\n" +
            "       BND.DEMAND_NO_ITEM AS \"demandNoItem\",\n" +
            "       BND.DEMANDER AS \"demander\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVABLE AS \"domesticQtyReceivable\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "       BND.OVERSEA_QTY_RECEIVED AS \"overseaQtyReceived\",\n" +
            "       BND.PACKAGE_QUANTITY_MAX AS \"packageQuantityMax\",\n" +
            "       BND.PACKAGE_UNIT_MAX AS \"packageUnitMax\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.ATTACH_FILE AS \"photos\"\n" +
            "FROM SAP_BOX_NOTE BN\n" +
            "    JOIN SAP_BOX_NOTE_DTL BND ON BN.BOX_NOTE = BND.BOX_NOTE\n" +
            "    JOIN MATERIAL_SAP MS ON BND.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN MATERIAL_PHOTOS MP ON MS.MATERIAL_NO = MP.MATERIAL_NO\n" +
            "WHERE BN.BOX_NOTE = #{boxNote}")
    List<Map<String, Object>> getDnMaterialDtl(@Param("boxNote") String boxNote);

    @Select("SELECT BN.BOX_NOTE AS \"boxNote\",\n" +
            "       BND.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       BND.ITEM AS \"item\",\n"+
            "       BND.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
            "       BND.MATERIAL_NO AS \"materialNo\",\n" +
            "       BND.BASIC_UNIT AS \"basicUnit\",\n" +
            "       BND.SPECS AS \"specs\",\n" +
            "       BND.QUANTITY AS \"qty\",\n" +
            "       BND.SRM_CONTRACT_NO AS \"srmContractNo\",\n" +
            "       BND.PURCHASE_NO AS \"purchaseNo\",\n" +
            "       BND.PURCHASE_NO AS \"orderNumber\",\n" +
            "       BND.PURCHASE_NO_ITEM AS \"orderItem\",\n" +
            "       BND.DEMAND_NO AS \"demandNo\",\n" +
            "       BND.DEMAND_NO_ITEM AS \"demandNoItem\",\n" +
            "       BND.DEMANDER AS \"demander\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVABLE AS \"domesticQtyReceivable\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "       BND.OVERSEA_QTY_RECEIVED AS \"overseaQtyReceived\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.ATTACH_FILE AS \"photos\"\n" +
            "FROM SAP_BOX_NOTE BN\n" +
            "    JOIN SAP_BOX_NOTE_DTL BND ON BN.BOX_NOTE = BND.BOX_NOTE\n" +
            "    JOIN MATERIAL_SAP MS ON BND.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN MATERIAL_PHOTOS MP ON MS.MATERIAL_NO = MP.MATERIAL_NO\n" +
            "WHERE BN.PACKAGE_NOTE = #{packageNote}")
    List<Map<String, Object>> getDnMaterialDtlByPackage(@Param("packageNote") String packageNote);

    @Select("<script> \n" +
            "SELECT BN.BOX_NOTE AS \"boxNote\",\n" +
            "       BND.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       BND.ITEM AS \"item\",\n"+
            "       BND.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
            "       BND.MATERIAL_NO AS \"materialNo\",\n" +
            "       BND.BASIC_UNIT AS \"basicUnit\",\n" +
            "       BND.SPECS AS \"specs\",\n" +
            "       BND.QUANTITY AS \"qty\",\n" +
            "       BND.SRM_CONTRACT_NO AS \"srmContractNo\",\n" +
            "       BND.PURCHASE_NO AS \"purchaseNo\",\n" +
            "       BND.DEMAND_NO AS \"demandNo\",\n" +
            "       BND.DEMAND_NO_ITEM AS \"demandNoItem\",\n" +
            "       BND.DEMANDER AS \"demander\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVABLE AS \"domesticQtyReceivable\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "       BND.OVERSEA_QTY_RECEIVED AS \"overseaQtyReceived\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.ATTACH_FILE AS \"photos\"\n" +
            "FROM SAP_BOX_NOTE BN\n" +
            "    JOIN SAP_BOX_NOTE_DTL BND ON BN.BOX_NOTE = BND.BOX_NOTE\n" +
            "    JOIN MATERIAL_SAP MS ON BND.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN MATERIAL_PHOTOS MP ON MS.MATERIAL_NO = MP.MATERIAL_NO\n" +
            "<when test='werks!=null'>AND SAP_BOX_NOTE.</when>"+
            "</script>")
    List<Map<String,Object>> getDnMaterialDtlLike(@Param("boxNote") String boxNote, @Param("werks") String werks, @Param("whsLocationCode") String whsLocationCode);

    @Select("SELECT COUNT(*) FROM SAP_BOX_NOTE WHERE BOX_NOTE= #{boxNote}")
    int validateBoxNote(@Param("boxNote") String boxNote);

    @Select("SELECT BOX_NOTE AS \"newBoxList\" FROM SAP_BOX_NOTE_DTL WHERE REMARK = #{boxNote}")
    List<Map<String,Object>> checkNewBox(@Param("boxNote") String boxNote);

//    @Select("SELECT TEMP.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
//            "       DND.DELIVERY_NOTE_SAP_ITEM AS \"deliveryNoteSapItem\",\n" +
//            "       TEMP.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
//            "       TEMP.QUANTITY AS \"quantity\",\n" +
//            "       DND.MATERIAL_NO AS \"materialNo\",\n" +
//            "       DND.DELIVERY_UNIT AS \"deliveryUnit\",\n" +
//            "       DND.WERKS AS \"werks\",\n" +
//            "       DND.WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
//            "FROM (\n" +
//            "         SELECT BND.DELIVERY_NOTE_SAP,\n" +
//            "                BND.DELIVERY_NOTE_SAP_ITEM,\n" +
//            "                SUM(DOMESTIC_QTY_RECEIVED) AS \"DOMESTIC_QTY_RECEIVED\",\n" +
//            "                SUM(QUANTITY) AS \"QUANTITY\"\n" +
//            "         FROM SAP_BOX_NOTE_DTL BND WHERE DELIVERY_NOTE_SAP = #{deliveryNoteSap} \n" +
//            "         GROUP BY BND.DELIVERY_NOTE_SAP_ITEM, BND.DELIVERY_NOTE_SAP ORDER BY DELIVERY_NOTE_SAP_ITEM\n" +
//            "     ) TEMP, SAP_DELIVERY_NOTE_DTL DND\n" +
//            "WHERE TEMP.DELIVERY_NOTE_SAP = DND.DELIVERY_NOTE_SAP AND DND.DELIVERY_NOTE_SAP_ITEM = (TEMP.DELIVERY_NOTE_SAP_ITEM * 10) ")
//    List<Map<String, Object>> getBoxNoteDtlToSap(@Param("deliveryNoteSap") String deliveryNoteSap);

    /** 拆箱任务生成新箱码时，获取一个序列 */
    @Select("SELECT BOX_NOTE_REPACKAGE.nextval FROM DUAL")
    int getNewBoxNote();
}