package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.WhsDump;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
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-06-29
 */
@Mapper
public interface WhsDumpMapper extends BaseMapper<WhsDump> {

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "         SELECT WD.WHS_DUMP_UUID AS \"whsDumpUuid\",\n" +
            "                WD.WHS_DUMP_NO AS \"whsDumpNo\",\n" +
            "                WD.GOODS_TYPE AS \"goodsType\",\n" +
            "                WD.BUSINESS_TYPE AS \"businessType\",\n" +
            "                WD.WERKS_DUMP_OUT AS \"werksDumpOut\",\n" +
            "                WD.WHS_LOCATION_CODE_DUMP_OUT AS \"whsLocationCodeDumpOut\",\n" +
            "                WD.DUMP_OUT_REMARK AS \"dumpOutRemark\",\n" +
            "                WD.CREATE_USERID AS \"createUserId\",\n" +
            "                WD.CREATE_DATE AS \"createDate\",\n" +
            "                WD.DUMP_OUT_OPERATOR AS \"dumpOutOperator\",\n" +
            "                WD.DUMP_OUT_DATE AS \"dumpOutDate\",\n" +
            "                WD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "                WD.DUMP_OUT_SAP_DATE AS \"dumpOutSapDate\",\n" +
            "                WD.SAP_DUMP_OUT_NO AS \"sapDumpOutNo\",\n" +
            "                WD.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "                WD.SAP_WHS_OUT_NO AS \"sapWhsOutNo\",\n" +
            "                WD.WERKS_DUMP_IN AS \"werksDumpIn\",\n" +
            "                WD.WHS_LOCATION_CODE_DUMP_IN AS \"whsLocationCodeDumpIn\",\n" +
            "                WD.DUMP_IN_REMARK AS \"dumpInRemark\",\n" +
            "                WD.DUMP_IN_OPERATOR AS \"dumpInOperator\",\n" +
            "                WD.DUMP_IN_DATE AS \"dumpInDate\",\n" +
            "                WD.SAP_WHS_IN_NO AS \"sapWhsInNo\",\n" +
            "                WD.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "                WD.VOUCHER_TYPE AS \"voucherType\",\n" +
            "                WD.PURCHASE_GROUP AS \"purchaseGroup\",\n" +
            "                WD.PURCHASE_ORG AS \"purchaseOrg\",\n" +
            "                WD.RETURN_MARK AS \"returnMark\",\n" +
            "                WD.REMARK AS \"remark\",\n" +
            "                WD.TRANSPORT_TYPE AS \"transportType\",\n" +
            "                WD.OA_NUMBER AS \"oaNumber\",\n" +
            "                WD.RECEIVE_WERKS AS \"receiveWerks\",\n" +
            "                WD.SALES_ORDER_NUMBER AS \"salesOrderNumber\",\n" +
            "                WD.ORDER_SAP_CLIENT AS \"sapClient\",\n" +
            "                WD.SALES_SAP_CLIENT AS \"salesSapClient\",\n" +
            "                WD.DN_SAP_CLIENT AS \"dnSapClient\",\n" +
            "                WD.DUMP_IN_SAP_DATE AS \"dumpInSapDate\"\n" +
            "         FROM WHS_DUMP WD\n" +
            "                  LEFT JOIN SPMS_USER U ON WD.CREATE_USERID = U.WORK_CODE " +
            "         WHERE WD.SPMS_STATUS != 0 " +
            "         <when test='whsDumpNo != null'> AND WD.WHS_DUMP_NO = #{whsDumpNo}</when> " +
            "         <when test='goodsType != null'> AND WD.GOODS_TYPE = #{goodsType}</when> " +
            "         <when test='werksDumpOut != null'> AND WD.WERKS_DUMP_OUT = #{werksDumpOut}</when> " +
            "         <when test='whsLocationCodeDumpOut != null'> AND WD.WHS_LOCATION_CODE_DUMP_OUT = #{whsLocationCodeDumpOut}</when> " +
            "         <when test='spmsStatus != null'> AND WD.SPMS_STATUS = #{spmsStatus}</when> " +
            "         <when test='sapDumpOutNo != null'> AND WD.SAP_DUMP_OUT_NO = #{sapDumpOutNo}</when> " +
            "         <when test='sapDeliveryNote != null'> AND WD.SAP_DELIVERY_NOTE = #{sapDeliveryNote}</when> " +
            "         <when test='werksDumpIn != null'> AND WD.WERKS_DUMP_IN = #{werksDumpIn}</when> " +
            "         <when test='transportType != null'> AND WD.TRANSPORT_TYPE = #{transportType}</when> " +
            "         <when test='whsLocationCodeDumpIn != null'> AND WD.WHS_LOCATION_CODE_DUMP_IN = #{whsLocationCodeDumpIn}</when> " +
            "         <when test='beginDate != null'> AND WD.CREATE_DATE &gt;= #{beginDate}</when> " +
            "         <when test='endDate != null'> AND WD.CREATE_DATE &lt;= #{endDate}</when> " +
            "  ORDER BY WD.CREATE_DATE DESC, WD.SPMS_STATUS ASC " +
            "         ) RE ORDER BY ROWNUM ASC" +
            "</script>")
    List<Map<String, Object>> pageWhsDumpDataMap(Page<Map<String, Object>> pages,
                                            @Param("whsDumpNo") String whsDumpNo,
                                            @Param("goodsType") String goodsType,
                                            @Param("werksDumpOut") String werksDumpOut,
                                            @Param("whsLocationCodeDumpOut") String whsLocationCodeDumpOut,
                                            @Param("spmsStatus") String spmsStatus,
                                            @Param("sapDumpOutNo") String sapDumpOutNo,
                                            @Param("sapDeliveryNote") String sapDeliveryNote,
                                            @Param("werksDumpIn") String werksDumpIn,
                                            @Param("whsLocationCodeDumpIn") String whsLocationCodeDumpIn,
                                            @Param("beginDate") String beginDate,
                                            @Param("endDate") String endDate, 
                                            @Param("transportType") String transportType);

    /** 空运人带任务提交SAP，转储表头数据获取，空运时采购组为250，人带为260 */
    @Select("SELECT REPLACE(WD.CREATE_DATE, '-', '') AS \"aedat\",\n" +
            "       WD.WERKS_DUMP_OUT AS \"werks\",\n" +
            "       CASE WHEN WD.TRANSPORT_TYPE = '0' THEN '250' ELSE WD.PURCHASE_GROUP END AS \"ekgrp\",\n" +
            "       WD.VOUCHER_TYPE AS \"bsart\",\n" +
            "       '' AS \"zterm\",\n" +
            "       'CN01' AS \"bukrs\",\n" +
            "       WD.PURCHASE_ORG AS \"ekorg\",\n" +
            "       WD.SAP_DUMP_OUT_NO AS \"ebeln\",\n" +
            "       WD.WHS_DUMP_NO AS \"SPMSID\",\n" +
            "       '空运人带单：' || WD.WHS_DUMP_NO || '(' || WD.WERKS_DUMP_OUT ||'-' || WD.WERKS_DUMP_IN || '-' || NVL(WD.RECEIVE_WERKS, '') || ')' AS \"zstring\"\n" +
            "FROM WHS_DUMP WD WHERE WHS_DUMP_UUID = #{whsDumpUuid} ")
    Map<String, Object> dataToDapHead (@Param("whsDumpUuid") String whsDumpUuid);

    /** 空运人带任务提交SAP，转储箱码信息获取 */
    @Select("SELECT WDD.BOX_NOTE AS \"packageCode\", '' AS \"flgYfp\"\n" +
            "FROM WHS_DUMP_DTL WDD WHERE WDD.WHS_DUMP_UUID = #{whsDumpUuid} \n" +
            "                        AND WDD.ACTUAL_OUT_QTY > 0 AND BOX_NOTE IS NOT NULL ")
    List<Map<String, Object>> dataToDapBoxNote (@Param("whsDumpUuid") String whsDumpUuid);

    /**
     * 空运人带任务提交SAP，转储物料信息获取
     * "       REPLACE(WD.DELIVERY_DATE, '-', '') AS \"eindt\",\n" +
     * ————20211019 交货日期字段使用 TO_CHAR(SYSDATE + 50, 'yyyyMMdd') 替换
     * */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"ebelp\",\n" +
            "       TO_CHAR(SYSDATE + 7, 'yyyyMMdd') AS \"eindt\",\n" +
            "       WD.WERKS_DUMP_OUT AS \"werks\",\n" +
            "       WD.WHS_LOCATION_CODE_DUMP_OUT AS \"lgort\",\n" +
            "       BND.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(BND.DOMESTIC_QTY_RECEIVED, 'fm9999999990.000') AS \"menge\",\n" +
            "       SAP.BASIC_UNIT AS \"meins\",\n" +
            "       '' AS \"loekz\",\n" +
            "       WDD.OVER_DELIVERY_LIMIT AS \"uebto\",\n" +
            "       'X' AS \"webre\",\n" +
            "       'ZNLC' AS \"lfart\",\n" +
            "       '01' AS \"vsbed\",\n" +
            "       '0001' AS \"ladgr\",\n" +
            "       '0001' AS \"tragr\",\n" +
            "       'CN01' AS \"vkorg\",\n" +
            "       '10' AS \"vtweg\",\n" +
            "       '00' AS \"spart\",\n" +
            "       WD.WERKS_DUMP_IN AS \"kunnr\"\n" +
            "FROM WHS_DUMP_DTL WDD\n" +
            "    LEFT JOIN WHS_DUMP WD ON WDD.WHS_DUMP_UUID = WD.WHS_DUMP_UUID\n" +
            "    LEFT JOIN SAP_BOX_NOTE BN ON WDD.BOX_NOTE = BN.BOX_NOTE\n" +
            "    LEFT JOIN SAP_BOX_NOTE_DTL BND ON BN.BOX_NOTE = BND.BOX_NOTE\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON BND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE WDD.WHS_DUMP_UUID = #{whsDumpUuid} AND WDD.ACTUAL_OUT_QTY > 0 AND BND.DOMESTIC_QTY_RECEIVED > 0 AND WDD.BOX_NOTE IS NOT NULL \n")
    List<Map<String, Object>> dataToDapMaterial (@Param("whsDumpUuid") String whsDumpUuid);
    
    /** Z_POS_PO_CREATE 空运人带提交SAP撤销PO，请求行项目结构 */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"PO_ITEM\"\n" +
            "FROM WHS_DUMP_DTL WDD\n" +
            "WHERE WDD.WHS_DUMP_UUID = #{whsDumpUuid}\n" +
            "  AND WDD.ACTUAL_OUT_QTY > 0 ORDER BY WDD.ITEM ASC ")
    List<Map<String, Object>> sapPoCancelDtl(@Param("whsDumpUuid") String whsDumpUuid);
    
    /** 获取PO单号后撤销同步SAP查询表头数据 **/
    @Select("SELECT W.COMPANY_CODE AS \"COMP_CODE\",\n" +
            "       OWD.VOUCHER_TYPE AS \"DOC_TYPE\",\n" +
            "       W2.SUPPLIER_NO AS \"VENDOR\",\n" +
            "       OWD.WERKS_DUMP_OUT AS \"SUPPL_PLNT\",\n" +
            "       REPLACE(OWD.DUMP_OUT_SAP_DATE, '-', '') AS \"CREAT_DATE\",\n" +
            "       OWD.PURCHASE_ORG AS \"PURCH_ORG\",\n" +
            "       OWD.SAP_DUMP_OUT_NO AS \"PO_NUMBER\",\n" +
            "       OWD.WHS_DUMP_NO AS \"ZID\",\n" +
            "       OWD.PURCHASE_GROUP AS \"PUR_GROUP\"\n" +
            "FROM WHS_DUMP OWD\n" +
            "    LEFT JOIN WERKS W ON OWD.WERKS_DUMP_IN = W.WERKS\n" +
            "    LEFT JOIN WERKS W2 ON OWD.WERKS_DUMP_OUT = W2.WERKS\n" +
            "WHERE OWD.WHS_DUMP_UUID = #{whsDumpUuid} ")
	Map<String, Object> selectWhsDumpHead(@Param("whsDumpUuid") String whsDumpUuid);
    
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"PO_ITEM\",\n" +
            "       BND.MATERIAL_NO AS \"MATERIAL\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"SHORT_TEXT\",\n" +
            "       OWD.WERKS_DUMP_IN AS \"PLANT\",\n" +
            "       TO_CHAR(OWDD.ACTUAL_OUT_QTY) AS \"QUANTITY\",\n" +
            "       SAP.BASIC_UNIT AS \"PO_UNIT\",\n" +
            "       REPLACE(OWD.DELIVERY_DATE, '-', '') AS \"DELIVERY_DATE\",\n" +
            "       OWD.WHS_DUMP_NO AS \"WHS_DUMP_NO\"\n" +
            "FROM WHS_DUMP_DTL OWDD\n" +
            "    LEFT JOIN WHS_DUMP OWD ON OWDD.WHS_DUMP_UUID = OWD.WHS_DUMP_UUID\n" +
            "    LEFT JOIN SAP_BOX_NOTE_DTL BND ON OWDD.BOX_NOTE = BND.BOX_NOTE\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON BND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE OWDD.WHS_DUMP_UUID = #{whsDumpUuid}\n" +
            "  AND OWDD.ACTUAL_OUT_QTY > 0 ORDER BY OWDD.ITEM ASC ")
	List<Map<String, Object>> selectWhsDumpDtl(@Param("whsDumpUuid") String whsDumpUuid);
    
    @Select("SELECT OWD.MATERIAL_NO AS \"materialNo\",\n" +
            "       OWD.BASIC_UNIT AS \"basicUnit\"\n" +
            "FROM MATERIAL_SAP OWD\n" +
            "WHERE OWD.MATERIAL_NO = #{materialNo} ")
	Map<String, Object> selectMaterial(@Param("materialNo") String materialNo);

}