package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaWhsDumpQueryDTO;
import com.sunda.spmsoversea.entity.OverseaWhsDump;
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-11-02
 */
@Mapper
public interface OverseaWhsDumpMapper extends BaseMapper<OverseaWhsDump> {

    @Select("<script>" +
            "SELECT OWD.WHS_DUMP_UUID AS \"whsDumpUuid\",\n" +
            "       OWD.WHS_DUMP_NO AS \"whsDumpNo\",\n" +
            "       OWD.WERKS_DUMP_IN AS \"werksDumpIn\",\n" +
            "       OWD.WHS_LOCATION_CODE_DUMP_IN AS \"whsLocationCodeDumpIn\",\n" +
            "       OWD.WERKS_DUMP_OUT AS \"werksDumpOut\",\n" +
            "       OWD.WHS_LOCATION_CODE_DUMP_OUT AS \"whsLocationCodeDumpOut\",\n" +
            "       OWD.CREATE_DATE AS \"createDate\",\n" +
            "       OWD.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWD.OA_WORKFLOW AS \"oaWorkflow\",\n" +
            "       OWD.OA_BIANHAO AS \"oaBianhao\",\n" +
            "       OWD.OA_REMARK AS \"oaRemark\",\n" +
            "       OWD.SUBMIT_OA_DATE AS \"submitOaDate\",\n" +
            "       OWD.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       OWD.DUMP_OUT_REMARK AS \"dumpOutRemark\",\n" +
            "       OWD.DUMP_OUT_DATE AS \"dumpOutDate\",\n" +
            "       OWD.SAP_PURCHASE_NO AS \"sapPurchaseNo\",\n" +
            "       OWD.PURCHASE_POSTING_DATE AS \"purchasePostingDate\",\n" +
            "       OWD.PURCHASE_VOUCHER_YEAR AS \"purchaseVoucherYear\",\n" +
            "       OWD.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWD.POSTING_DATE AS \"postingDate\",\n" +
            "       OWD.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OWD.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OWD.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OWD.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OWD.CREATE_USERID AS \"createUserid\",\n" +
            "       OWD.APPROVE_FLAG AS \"approveFlag\",\n" +
            "       OWD.SALES_ORDER_NUMBER AS \"salesOrderNumber\",\n" +
            "       OWD.TRANSNATIONAL AS \"transnational\",\n" +
            "       OWD.TRANSPORT_TYPE AS \"transportType\",\n" +
            "       OWD.DATA_VERSION AS \"dataVersion\"\n" +
            "FROM OVERSEA_WHS_DUMP OWD\n" +
            "WHERE 1=1\n" +
            "  <when test='dto.whsDumpNo!=null and dto.whsDumpNo != \"\"'> AND OWD.WHS_DUMP_NO = #{dto.whsDumpNo}</when>" +
            "  <when test='dto.werksDumpIn!=null and dto.werksDumpIn != \"\"'> AND OWD.WERKS_DUMP_IN = #{dto.werksDumpIn}</when>" +
            "  <when test='dto.whsLocationCodeDumpIn!=null and dto.whsLocationCodeDumpIn != \"\"'> AND OWD.WHS_LOCATION_CODE_DUMP_IN = #{dto.whsLocationCodeDumpIn}</when>" +
            "  <when test='dto.werksDumpOut!=null and dto.werksDumpOut != \"\"'> AND OWD.WERKS_DUMP_OUT = #{dto.werksDumpOut}</when>" +
            "  <when test='dto.whsLocationCodeDumpOut!=null and dto.whsLocationCodeDumpOut != \"\"'> AND OWD.WHS_LOCATION_CODE_DUMP_OUT = #{dto.whsLocationCodeDumpOut}</when>" +
            "  <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND OWD.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
            "  <when test='dto.endDate!=null and dto.endDate != \"\"'> AND OWD.CREATE_DATE &lt;= #{dto.endDate}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OWD.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.sapPurchaseNo!=null and dto.sapPurchaseNo != \"\"'> AND OWD.SAP_PURCHASE_NO = #{dto.sapPurchaseNo}</when>" +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND OWD.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote}</when>" +
            " <when test='dto.werksReceiveList != null'> AND OWD.WERKS_DUMP_IN in \n" +
            "    <foreach item='item' index='index' collection='dto.werksReceiveList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "    </foreach>\n" +
            " </when>\n" +
            " <when test='dto.werksDeliveryList != null'> AND OWD.WERKS_DUMP_OUT in \n" +
            "    <foreach item='item' index='index' collection='dto.werksDeliveryList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "    </foreach>\n" +
            " </when>\n" +
            "  AND OWD.SPMS_STATUS != '0' ORDER BY OWD.WERKS_DUMP_IN ASC, OWD.WHS_LOCATION_CODE_DUMP_IN ASC, OWD.CREATE_DATE " +
            "</script>")
    List<Map<String, Object>> getWhsDumpPage(IPage page, @Param("dto") OverseaWhsDumpQueryDTO dto);

    @Select("SELECT MAX(ITEM) FROM OVERSEA_WHS_DUMP_DTL WHERE WHS_DUMP_UUID = #{whsDumpUuid}")
    Integer getWhsDumpMaxItemNumber(@Param("whsDumpUuid") String whsDumpUuid);

    /** Z_SPMS_CREATE_STO 转储任务提交SAP获取PO，请求表头结构 */
//    @Select("SELECT REPLACE(OWD.CREATE_DATE, '-', '') AS \"aedat\",\n" +
//            "       OWD.WERKS_DUMP_OUT AS \"werks\",\n" +
//            "       OWD.PURCHASE_GROUP AS \"ekgrp\",\n" +
//            "       OWD.VOUCHER_TYPE AS \"bsart\",\n" +
//            "       '' AS \"zterm\",\n" +
//            "       W.COMPANY_CODE AS \"bukrs\",\n" +
//            "       OWD.PURCHASE_ORG AS \"ekorg\",\n" +
//            "       OWD.SAP_PURCHASE_NO AS \"ebeln\",\n" +
//            "       '海外转储单：' || OWD.WHS_DUMP_NO AS \"zstring\"\n" +
//            "FROM OVERSEA_WHS_DUMP OWD LEFT JOIN WERKS W ON OWD.WERKS_DUMP_OUT = W.WERKS\n" +
//            "WHERE OWD.WHS_DUMP_UUID = #{whsDumpUuid} ")
//    Map<String, Object> getPoHead(@Param("whsDumpUuid") String whsDumpUuid);

    
    @Select("SELECT W2.COMPANY_CODE AS \"BUKRS_S\",\n" +
    		"       OWD.WHS_DUMP_NO AS \"LYDJH\",\n" +
            "       OWD.WERKS_DUMP_OUT AS \"WERKS_S\",\n" +
            "       OWD.WHS_LOCATION_CODE_DUMP_OUT AS \"LGORT_S\",\n" +
            "       W.COMPANY_CODE AS \"BUKRS_R\",\n" +
            "       OWD.WERKS_DUMP_IN AS \"WERKS_R\",\n" +
            "       OWD.WHS_LOCATION_CODE_DUMP_IN AS \"LGORT_R\",\n" +
            "       OWD.CREATE_USERID AS \"S_USER\",\n" +
            "       REPLACE(OWD.CREATE_DATE, '-', '') AS \"DATE_CR\",\n" +
            "       '03' AS \"TYPE_D\",\n" +
            "       (CASE WHEN OWD.TRANSPORT_TYPE = '0' THEN '10' ELSE '40' END) AS \"TYPE_T\",\n" +
            "       '' AS \"REMARKS\"\n" +
            "FROM OVERSEA_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> getSapPoHead(@Param("whsDumpUuid") String whsDumpUuid);

    /** 转储任务提交SAP获取PO，请求行项目结构 */
//    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"ebelp\",\n" +
//            "       TO_CHAR(SYSDATE + 7, 'yyyyMMdd') AS \"eindt\",\n" +
//            "       OWD.WERKS_DUMP_OUT AS \"werks\",\n" +
//            "       OWD.WHS_LOCATION_CODE_DUMP_OUT AS \"lgort\",\n" +
//            "       OWDD.MATERIAL_NO AS \"matnr\",\n" +
//            "       TO_CHAR(OWDD.ACTUAL_QTY_BASIC_UNIT) AS \"menge\",\n" +
//            "       OWDD.BASIC_UNIT AS \"meins\",\n" +
//            "       '' AS \"loekz\",\n" +
//            "       '' AS \"uebto\",\n" +
//            "       'X' AS \"webre\",\n" +
//            "       '' AS \"lfart\",\n" +
//            "       '' AS \"vsbed\",\n" +
//            "       '' AS \"ladgr\",\n" +
//            "       '' AS \"tragr\",\n" +
//            "       '' AS \"vkorg\",\n" +
//            "       '' AS \"vtweg\",\n" +
//            "       '' AS \"spart\",\n" +
//            "       '' AS \"kunnr\"\n" +
//            "FROM OVERSEA_WHS_DUMP_DTL OWDD\n" +
//            "    LEFT JOIN OVERSEA_WHS_DUMP OWD ON OWDD.WHS_DUMP_UUID = OWD.WHS_DUMP_UUID\n" +
//            "WHERE OWDD.WHS_DUMP_UUID = #{whsDumpUuid}\n" +
//            "  AND OWDD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
//            "ORDER BY OWDD.ITEM ASC ")
//    List<Map<String, Object>> getPoItem(@Param("whsDumpUuid") String whsDumpUuid);

    @Select("SELECT TO_CHAR(OWDD.ITEM) AS \"PONSR_S\",\n" +
            "       OWD.WHS_DUMP_NO AS \"LYDJH\",\n" +
            "       OWDD.MATERIAL_NO AS \"MATNR\",\n" +
            "       '' AS \"PACKAGE_CODE\",\n" +
            "       TO_CHAR(OWDD.ACTUAL_QTY_BASIC_UNIT) AS \"AMENG\",\n" +
            "       OWDD.BASIC_UNIT AS \"MEINS\",\n" +
            		// 件数
            "       '' AS \"PEMNG\",\n" +
            "       OWDD.UNIT_SALES AS \"VRKME\",\n" +
            "       OWDD.ACTUAL_REMARK AS \"REMARKS\"\n" +
            "FROM OVERSEA_WHS_DUMP_DTL OWDD\n" +
            "    LEFT JOIN OVERSEA_WHS_DUMP OWD ON OWDD.WHS_DUMP_UUID = OWD.WHS_DUMP_UUID\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON OWDD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE OWDD.WHS_DUMP_UUID = #{whsDumpUuid}\n" +
            "  AND OWDD.ACTUAL_QTY_BASIC_UNIT > 0 ORDER BY OWDD.ITEM ASC ")
    List<Map<String, Object>> getSapPoItem(@Param("whsDumpUuid") String whsDumpUuid);

    /** Z_POS_PO_CREATE 转储任务提交SAP撤销PO，请求行项目结构 */
    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"PO_ITEM\"\n" +
            "FROM OVERSEA_WHS_DUMP_DTL OWDD\n" +
            "WHERE OWDD.WHS_DUMP_UUID = #{whsDumpUuid}\n" +
            "  AND OWDD.ACTUAL_QTY_BASIC_UNIT > 0 ORDER BY OWDD.ITEM ASC ")
    List<Map<String, Object>> sapPoCancelItem(@Param("whsDumpUuid") String whsDumpUuid);
    
    @Select("SELECT ORQ.WHS_DUMP_UUID AS \"whsDumpUuid\",\n" +
            "       ORQ.WHS_DUMP_NO AS \"whsDumpNo\",\n" +
            "       ORQ.WERKS_DUMP_IN AS \"werksDumpIn\",\n" +
            "       ORQ.WHS_LOCATION_CODE_DUMP_IN AS \"whsLocationCodeDumpIn\",\n" +
            "       ORQ.WERKS_DUMP_OUT AS \"werksDumpOut\",\n" +
            "       ORQ.WHS_LOCATION_CODE_DUMP_OUT AS \"whsLocationCodeDumpOut\",\n" +
            "       ORQ.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ORQ.CREATE_DATE AS \"createDate\",\n" +
            "       ORQ.DUMP_OUT_REMARK AS \"dumpOutRemark\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       SU.DEPT_NAME AS \"deptName\",\n" +
            "       ORQ.SAP_PURCHASE_NO AS \"sapPurchaseNo\",\n" +
            "       ORQ.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ORQ.VOUCHER_TYPE AS \"voucherType\",\n" +
            "       ORQ.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ORQ.CREATE_USERID AS \"createUserid\",\n" +
            "       ORQ.TRANSNATIONAL AS \"transnational\",\n" +
            "       ORQ.TRANSPORT_TYPE AS \"transportType\" \n" +
            "FROM OVERSEA_WHS_DUMP ORQ " +
            "   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE ORQ.SPMS_STATUS != '0' " +
            "   AND ORQ.WHS_DUMP_UUID = #{whsDumpUuid}")
    Map<String, Object> getOverseaWhsDump(@Param("whsDumpUuid") String whsDumpUuid);
}