package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaWhsInQueryDTO;
import com.sunda.spmsoversea.entity.OverseaWhsIn;
import com.sunda.spmsoversea.entity.OverseaWhsInDtl;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

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

/**
 * <p>
 * 海外仓入库任务表头 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-08-26
 */
@Mapper
public interface OverseaWhsInMapper extends BaseMapper<OverseaWhsIn> {

    @Select("<script>" +
            "SELECT OWI.UUID_WHS_IN AS \"uuidWhsIn\",\n" +
            "       OWI.WHS_IN_NO AS \"whsInNo\",\n" +
            "       OWI.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OWI.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWI.WERKS AS \"werks\",\n" +
            "       OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWI.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWI.CREATE_DATE AS \"createDate\",\n" +
            "       OWI.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       OWI.REMARKS AS \"remarks\",\n" +
            "       OWI.COMMENTS AS \"comments\",\n" +
            "       OWI.POSTING_DATE AS \"postingDate\",\n" +
            "       OWI.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OWI.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OWI.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OWI.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OWI.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OWI.DATA_VERSION AS \"dataVersion\",\n" +
            "       OWI.CREATE_TIME AS \"createTime\",\n" +
            "       OWI.UPDATE_TIME AS \"updateTime\",\n" +
            "       OWI.PDA_SN AS \"pdaSN\",\n" +
            "       OWI.PDA_TIME AS \"pdaTime\",\n" +
            "       OWI.CABINET_NUMBER AS \"cabinetNumber\"\n" +
            "FROM OVERSEA_WHS_IN OWI " +
            "   LEFT JOIN SPMS_USER SU ON OWI.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE 1=1 \n" +
            "  <when test='dto.uuidWhsIn!=null and dto.uuidWhsIn != \"\"'> AND OWI.UUID_WHS_IN = #{dto.uuidWhsIn}</when>" +
            "  <when test='dto.whsInNo!=null and dto.whsInNo != \"\"'> AND OWI.WHS_IN_NO = #{dto.whsInNo}</when>" +
            "  <when test='dto.sapPackageList!=null and dto.sapPackageList != \"\"'> AND OWI.SAP_PACKAGE_LIST = #{dto.sapPackageList}</when>" +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND OWI.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote}</when>" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND OWI.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND OWI.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OWI.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.cabinetNumber!=null and dto.cabinetNumber != \"\"'> AND OWI.CABINET_NUMBER = #{dto.cabinetNumber}</when>" +
            "  <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND OWI.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
            "  <when test='dto.endDate!=null and dto.endDate != \"\"'> AND OWI.CREATE_DATE &lt;= #{dto.endDate}</when>" +
            "  <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
            "     AND INSTR(OWI.CREATE_USERID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
            "  </when>" +
            " <when test='dto.werksList != null'> AND OWI.WERKS in \n" +
            "    <foreach item='item' index='index' collection='dto.werksList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "    </foreach>\n" +
            " </when>\n" +
            "  AND OWI.SPMS_STATUS != '7' \n" +
            "ORDER BY OWI.SPMS_STATUS ASC \n" +
            "</script>")
    List<Map<String, Object>> getOverseaWhsInPage(IPage page, @Param("dto") OverseaWhsInQueryDTO dto);

    @Select("SELECT OWI.SAP_DELIVERY_NOTE AS \"XBLNR\",\n" +
            "       OWI.SAP_DELIVERY_NOTE AS \"VBELN\",\n" +
            "       OWI.WHS_IN_NO AS \"LYDJH\",\n" +
            "       REPLACE(OWI.CREATE_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "       OWI.CREATE_USERID AS \"CREATEDBY\",\n" +
            "       '101' AS \"BWART\",\n" +
            //"       OWI.WHS_IN_NO || OWI.REMARKS AS \"SPMSID\",\n" +
            "       OWI.WHS_IN_NO || OWI.REMARKS AS \"BKTXT\"\n" +
            "FROM OVERSEA_WHS_IN OWI\n" +
            "WHERE OWI.UUID_WHS_IN = #{uuidWhsIn}")
    Map<String, Object> getWhsInToSap(@Param("uuidWhsIn") String uuidWhsIn, @Param("postingDate") String postingDate);

    @Select("SELECT OWID.SAP_DELIVERY_NOTE AS \"VBELN_IM\",\n" +
            "       OWID.SAP_DELIVERY_NOTE_ITEM AS \"VBELP_IM\",\n" +
            "       OWID.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(OWID.ACTUAL_QTY_BASIC_UNIT) AS \"BSTMG\",\n" +
            "       OWID.BASIC_UNIT AS \"BSTME\",\n" +
            "       OWI.WERKS AS \"WERKS\",\n" +
            "       OWI.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       ODND.PURCHASE_NO AS \"EBELN\",\n" +
            "       ODND.PURCHASE_NO_ITEM AS \"EBELP\",\n" +
            "       NVL(OWID.ITEM_REMARKS, ' ') AS \"SGTXT\"\n" +
            "FROM OVERSEA_WHS_IN_DTL OWID\n" +
            "    LEFT JOIN OVERSEA_WHS_IN OWI ON OWID.UUID_WHS_IN = OWI.UUID_WHS_IN\n" +
            "    LEFT JOIN OVERSEA_DELIVERY_NOTE_DTL ODND ON OWID.SAP_DELIVERY_NOTE = ODND.SAP_DELIVERY_NOTE AND OWID.SAP_DELIVERY_NOTE_ITEM = ODND.SAP_DELIVERY_NOTE_ITEM\n" +
            "WHERE OWI.UUID_WHS_IN = #{uuidWhsIn} AND OWID.ACTUAL_QTY_BASIC_UNIT > 0 \n" +
            "ORDER BY OWID.SAP_DELIVERY_NOTE_ITEM ASC ")
    List<Map<String, Object>> getWhsInDtlToSap(@Param("uuidWhsIn") String uuidWhsIn);

    @Select("SELECT ODND.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODND.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveryNoteItem\",\n" +
            "       ODND.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       ODND.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
            "       NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) AS \"actualQtyBasicUnit\",\n" +
            "       (ODND.RECEIVABLE_QTY_BASIC_UNIT - NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0)) AS \"remainingReceivableQtyBasicUnit\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "    LEFT JOIN (\n" +
            "        SELECT OWI.SAP_DELIVERY_NOTE,\n" +
            "               OWID.SAP_DELIVERY_NOTE_ITEM,\n" +
            "               SUM(NVL(OWID.ACTUAL_QTY_BASIC_UNIT, 0)) AS ACTUAL_QTY_BASIC_UNIT\n" +
            "        FROM OVERSEA_WHS_IN_DTL OWID\n" +
            "            LEFT JOIN OVERSEA_WHS_IN OWI ON OWID.UUID_WHS_IN = OWI.UUID_WHS_IN\n" +
            "        WHERE OWI.SPMS_STATUS IN ('3', '4', '5') AND OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "        GROUP BY OWI.SAP_DELIVERY_NOTE, OWID.SAP_DELIVERY_NOTE_ITEM\n" +
            "        ) RE ON ODND.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "                    AND ODND.SAP_DELIVERY_NOTE_ITEM = RE.SAP_DELIVERY_NOTE_ITEM\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ODND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "ORDER BY TO_NUMBER(ODND.SAP_DELIVERY_NOTE_ITEM) ASC ")
    List<Map<String, Object>> getOverchargedDnItem(@Param("sapDeliveryNote") String sapDeliveryNote);

    @Select("SELECT OWI.UUID_WHS_IN AS \"uuidWhsIn\",\n" +
            "       OWI.WHS_IN_NO AS \"whsInNo\",\n" +
            "       OWI.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OWI.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWI.WERKS AS \"werks\",\n" +
            "       OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWI.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWI.CREATE_DATE AS \"createDate\",\n" +
            "       OWI.CREATE_USERID AS \"createUserId\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       OWI.REMARKS AS \"remarks\",\n" +
            "       OWI.COMMENTS AS \"comments\",\n" +
            "       OWI.POSTING_DATE AS \"postingDate\",\n" +
            "       OWI.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OWI.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OWI.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OWI.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OWI.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OWI.DATA_VERSION AS \"dataVersion\",\n" +
            "       OWI.CREATE_TIME AS \"createTime\",\n" +
            "       OWI.UPDATE_TIME AS \"updateTime\",\n" +
            "       OWI.CABINET_NUMBER AS \"cabinetNumber\"\n" +
            "FROM OVERSEA_WHS_IN OWI " +
            "   LEFT JOIN SPMS_USER SU ON OWI.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE 1=1 \n" +
            "  AND OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote}" +
            "  AND OWI.SPMS_STATUS != '7' \n" +
            "ORDER BY OWI.SPMS_STATUS ASC"
            )
    List<Map<String, Object>> getOverseaWhsInList(@Param("sapDeliveryNote") String sapDeliveryNote);
    
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update OVERSEA_WHS_IN_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where UUID_WHS_IN = #{item.uuidWhsIn,jdbcType=VARCHAR} \n" +
    		"		and SAP_DELIVERY_NOTE_ITEM = #{item.sapDeliveryNoteItem,jdbcType=VARCHAR} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<OverseaWhsInDtl> list);

}