package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmsoversea.dto.OverseaConsumptionDTO;
import com.sunda.spmsoversea.dto.OverseaReport;
import com.sunda.spmsoversea.dto.WelcomePageSearchDTO;
import com.sunda.spmsoversea.dto.WelcomePageWerksOrderDTO;
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;

@Mapper
public interface OverseaReportMapper {

    /** 获取指定工厂仓库的物料库存，不带储位按料号求和 */
    @Select("<script>" +
            "SELECT WS.WERKS AS \"werks\",\n" +
            "       WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "       SUM(WSI.QUANTITY) AS \"quantity\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\"\n" +
            "FROM WHS_STORAGE_INVENTORY WSI\n" +
            "       LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID " +
            "       LEFT JOIN MATERIAL_SAP MS ON WSI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "WHERE WSI.MATERIAL_NO IS NOT NULL AND WSI.QUANTITY > 0 \n" +
            " <when test='werks!=null and werks != \"\"'> AND WS.WERKS = #{werks}</when>" +
            " <when test='whsLocationCode!=null and whsLocationCode != \"\"'> AND WS.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " <when test='materialNo!=null and materialNo != \"\"'> AND WSI.MATERIAL_NO = #{materialNo}</when>" +
            "GROUP BY WS.WERKS, WS.WHS_LOCATION_CODE, WSI.MATERIAL_NO, MS.MATERIAL_ZH_DESC, MS.MATERIAL_EN_DESC, MS.BASIC_UNIT\n" +
            "ORDER BY WS.WERKS, WS.WHS_LOCATION_CODE, WSI.MATERIAL_NO" +
            "</script>")
    List<Map<String, Object>> getWhsInventory(@Param("werks") String werks,
                                              @Param("whsLocationCode") String whsLocationCode,
                                              @Param("materialNo") String materialNo);

    /** 获取指定工厂仓库下，指定料号每天的操作日志 */
    @Select("<script>" +
            "SELECT " +
            "       RE.MATERIAL_NO AS \"materialNo\",\n" +
            "       SUM(QUANTITY) AS \"quantity\",\n" +
            "       RE.CREATE_DATE AS \"createDate\",\n" +
            "       TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-mm-dd') - TO_DATE(RE.CREATE_DATE,'yyyy-mm-dd')) AS \"days\",\n" +
            "       WERKS AS \"werks\",\n" +
            "       WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
            "FROM (\n" +
            "    SELECT WOL.MATERIAL_NO, WOL.QUANTITY,\n" +
            "        SUBSTR(TO_CHAR(WOL.CREATE_TIME, 'yyyy-MM-dd'), 0, 10) AS \"CREATE_DATE\",\n" +
            "        WOL.WERKS, WOL.WHS_LOCATION_CODE\n" +
            "    FROM WHS_OPERATE_LOG WOL\n" +
            "    WHERE WOL.WERKS = #{werks}\n" +
            " <when test='whsLocationCode!=null and whsLocationCode != \"\"'>AND WOL.WHS_LOCATION_CODE = #{whsLocationCode}</when>\n" +
            " <when test='materialNo!=null and materialNo != \"\"'>AND WOL.MATERIAL_NO = #{materialNo}</when>\n" +
            "        AND WOL.DOCUMENT_TYPE IN ('2', '4')\n" +
            "        AND WOL.OPERATION_TYPE IN ('2', '7', '12', '18', '19')\n" +
            "    ) RE\n" +
            "GROUP BY RE.MATERIAL_NO, \"CREATE_DATE\", WERKS, WHS_LOCATION_CODE\n" +
            "ORDER BY TO_DATE(\"CREATE_DATE\", 'yyyy-MM-dd') DESC" +
            "</script>")
    /** 库龄分析(按物料) */
    List<Map<String, Object>> getWhsOperate(@Param("werks") String werks,
                                            @Param("whsLocationCode") String whsLocationCode,
                                            @Param("materialNo") String materialNo);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*,\n" +
            "       TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-mm-dd') -  TO_DATE(RE.\"createDate\", 'yyyy-mm-dd')) AS \"days\"\n" +
            "FROM (\n" +
            "    SELECT WS.WERKS AS \"werks\",\n" +
            "        WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "        WS.STORAGE_NO AS \"storageNo\",\n" +
            "        WSI.BOX_NOTE AS \"boxNote\",\n" +
            "        WSI.QUANTITY AS \"quantity\",\n" +
            "        (CASE WHEN WSI.UPDATE_TIME IS NULL THEN TO_CHAR(WSI.CREATE_TIME,'yyyy-MM-dd')\n" +
            "            ELSE TO_CHAR(WSI.UPDATE_TIME,'yyyy-MM-dd') END) AS \"createDate\"\n" +
            "    FROM WHS_STORAGE_INVENTORY WSI\n" +
            "        LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "WHERE BOX_NOTE IS NOT NULL AND WS.WERKS = #{werks} AND WSI.QUANTITY > 0\n" +
            "    ORDER BY WS.WHS_LOCATION_CODE, \"createDate\" ASC\n" +
            "    ) RE WHERE 1=1\n" +
            "           AND RE.\"werks\" = #{werks}\n" +
            "       AND TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-mm-dd') -  TO_DATE(RE.\"createDate\", 'yyyy-mm-dd')) &gt;= TO_NUMBER(#{days}) \n" +
            " <when test='whsLocationCode!=null and whsLocationCode != \"\"'>AND RE.\"whsLocationCode\" = #{whsLocationCode}</when>\n" +
            " <when test='boxNote!=null and boxNote != \"\"'>AND RE.\"boxNote\" = #{boxNote}</when>\n" +
            "</script>")
    /** 库龄分析(按箱码) */
    List<Map<String, Object>> getBoxInventoryAge(@Param("werks") String werks,
                                                 @Param("whsLocationCode") String whsLocationCode,
                                                 @Param("boxNote") String boxNote,
                                                 @Param("days") Integer days);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "     SELECT OWO.WERKS AS \"werks\",\n" +
            "            OWO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "            OWOD.MATERIAL_NO AS \"materialNo\",\n" +
            "            COUNT(OWOD.MATERIAL_NO) AS \"numberOfWhsOut\",\n" +
            "            SUM(OWOD.ACTUAL_QTY_BASIC_UNIT) AS \"quantity\",\n" +
            "            MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "            MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "            MS.BASIC_UNIT AS \"basicUnit\"\n" +
            "     FROM OVERSEA_WHS_OUT OWO\n" +
            "         LEFT JOIN OVERSEA_WHS_OUT_DTL OWOD ON OWO.UUID_WHS_OUT = OWOD.UUID_WHS_OUT\n" +
            "         LEFT JOIN MATERIAL_SAP MS ON OWOD.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "     WHERE OWO.SPMS_STATUS IN ('2', '3', '4') AND OWOD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "       <when test='dto.werks!=null and dto.werks != \"\"'>AND OWO.WERKS = #{dto.werks} </when>\n" +
            "       <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'>AND OWO.WHS_LOCATION_CODE = #{dto.whsLocationCode} </when>\n" +
            "       <when test='dto.materialNo!=null and dto.materialNo != \"\"'>AND OWOD.MATERIAL_NO = #{dto.materialNo} </when>\n" +
            "       <when test='dto.endDate!=null and dto.endDate != \"\"'>AND TO_DATE(OWO.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
            "       <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(OWO.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>" +
            "     GROUP BY OWO.WERKS, OWO.WHS_LOCATION_CODE, OWOD.MATERIAL_NO, MS.MATERIAL_ZH_DESC, MS.MATERIAL_EN_DESC, MS.BASIC_UNIT\n" +
            "     ORDER BY OWO.WERKS, OWO.WHS_LOCATION_CODE, COUNT(OWOD.MATERIAL_NO) DESC, OWOD.MATERIAL_NO\n" +
            "         ) RE" +
            "</script>")
    /** 获取领用出库——出库任务，耗用次数记录 */
    List<Map<String, Object>> getConsumptionRecord(@Param("dto") OverseaConsumptionDTO dto);

    @Select("SELECT COUNT(UUID)\n" +
            "FROM ORDER_WORKSHOP OWS\n" +
            "WHERE OWS.STATUS = '2'\n" +
            "  AND OWS.WERKS IN (SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "ORDER BY OWS.WERKS ")
    /** 1.需求提报单审批：获取当前人关联工厂下待审批的车间请购单数量；跨工厂合并，全周期； */
    Integer orderWorkshopToDoItemsMap(@Param("userId") String userId);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.STATUS AS \"status\",\n" +
            "       RE.QUANTITY AS \"quantity\"\n" +
            "FROM (\n" +
            "    SELECT COUNT(STATUS) AS \"QUANTITY\", STATUS\n" +
            "    FROM ORDER_WERKS OW\n" +
            "    WHERE OW.STATUS IN ('1', '4', '6', '8')\n" +
            "      AND OW.WORK_CODE = #{userId}\n" +
            "      AND OW.WERKS IN (\n" +
            "          SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "    GROUP BY STATUS\n" +
            "    ORDER BY TO_NUMBER(OW.STATUS)\n" +
            "    ) RE ")
    /** 2.工厂请购单：获取本人创建的待提交/待重提SRM/OA审批不通过的工厂请购单数量；跨工厂合并，全周期；
                    各状态单据量；跨工厂合并、近30天； */
    List<Map<String, Object>> orderWerksToDoItemsMap(@Param("userId") String userId);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.STATUS AS \"status\",\n" +
            "       RE.QUANTITY AS \"quantity\"\n" +
            "FROM (\n" +
            "    SELECT COUNT(SPMS_ID) AS \"QUANTITY\", STATUS\n" +
            "    FROM ORDER_WERKS OW\n" +
            "    WHERE OW.STATUS != '0'\n" +
            "      AND OW.WORK_CODE = #{userId}\n" +
            "      AND OW.WERKS IN (\n" +
            "          SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "      AND TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-mm-dd') - TO_DATE(OW.APPLICATION_DATE,'yyyy-mm-dd')) <= 30\n" +
            "    GROUP BY STATUS\n" +
            "    ORDER BY TO_NUMBER(OW.STATUS)\n" +
            "    ) RE ")
    /** 2.工厂请购单：获取本人创建的待提交/待重提SRM/OA审批不通过的工厂请购单数量；跨工厂合并，全周期；
     各状态单据量；跨工厂合并、近30天； */
    List<Map<String, Object>> orderWerksQuantityEachStatus(@Param("userId") String userId);

    @Select("SELECT COUNT(APPLICATION_UUID) \n" +
            "FROM MATERIAL_APPLICATION MA\n" +
            "WHERE MA.SPMS_STATUS IN ('1', '3')\n" +
            "  AND MA.USER_ID = #{userId}\n" +
            "  AND MA.WERKS IN (\n" +
            "    SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW \n" +
            "    WHERE SUW.WORK_CODE = #{userId})")
    /** 3.非目录物料申请单：待提交——获取本人创建的非目录物料申请待提交OA的数量；跨工厂合并，全周期； */
    Integer materialApplicationToDoItems(@Param("userId") String userId);

    @Select("SELECT COUNT(1)\n" +
            "FROM MATERIAL_APPLICATION MA\n" +
            "    LEFT JOIN MATERIAL_APPLICATION_DTL MAD ON MA.APPLICATION_UUID = MAD.APPLICATION_UUID\n" +
            "WHERE MA.SPMS_STATUS = '4'\n" +
            "  AND MA.USER_ID = #{userId}\n" +
            "  AND MA.WERKS IN (\n" +
            "      SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "  AND MAD.MATERIAL_NO IS NULL ")
    /** 3.编码未发放——获取本人创建的非目录物料申请，编码为空的明细行数；跨工厂合并，全周期； */
    Integer materialApplicationWithoutMaterialNo(@Param("userId") String userId);

    @Select("SELECT COUNT(1) FROM OVERSEA_REQUISITION ORQ\n" +
            "WHERE ORQ.SPMS_STATUS = '2'\n" +
            "  AND ORQ.WERKS IN (\n" +
            "      SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW\n" +
            "      WHERE SUW.WORK_CODE = #{userId}) ")
    /** 4.车间领用单：待审批——获取当前人关联工厂待审批的领用单；跨工厂合并，全周期； */
    Integer overseaRequisitionToDoItems(@Param("userId") String userId);

    @Select("SELECT COUNT(1)\n" +
            "FROM OVERSEA_REQUISITION ORQ\n" +
            "WHERE ORQ.SPMS_STATUS = '3'\n" +
            "  AND ORQ.CREATE_USERID = #{userId}\n" +
            "  AND ORQ.WERKS IN (\n" +
            "      SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW\n" +
            "      WHERE SUW.WORK_CODE = #{userId}) ")
    /** 4.待领用——获取本人创建的审批通过但未生成出库任务的领用任务；跨工厂合并，全周期； */
    Integer overseaRequisitionWithoutOut(@Param("userId") String userId);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.STATUS AS \"status\",\n" +
            "       RE.QUANTITY AS \"quantity\"\n" +
            "FROM (\n" +
            "    SELECT COUNT(OWS.SPMS_WS_ID) AS \"QUANTITY\", OWS.STATUS\n" +
            "    FROM ORDER_WORKSHOP OWS\n" +
            "    WHERE OWS.STATUS IN ('1', '2', '3')\n" +
            "      AND OWS.WORK_CODE = #{userId}\n" +
            "      AND OWS.WERKS IN (\n" +
            "          SELECT SUW.WERKS FROM SPMS_USER_WORKSHOP SUW WHERE SUW.WORK_CODE = #{userId})\n" +
            "    GROUP BY OWS.STATUS\n" +
            "    ) RE ")
    /** 5.获取本人创建的需求申请单，待提交 和 审批不通过、已提交三个状态下数量，本人关联工厂内数据；跨工厂合并，全周期； */
    List<Map<String, Object>> orderWorkshopQuantityEachStatus(@Param("userId") String userId);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "    SELECT DISTINCT OWS.UUID AS \"orderWorkshopUuid\",\n" +
            "                    OWS.WERKS AS \"werks\",\n" +
            "                    OWS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "                    OWS.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "                    OWS.URGENCY AS \"urgency\",\n" +
            "                    OWS.CREATE_DATE AS \"createDate\",\n" +
            "                    OWS.STATUS AS \"statusOrderWorkshop\",\n" +
            "                    OWS.WORK_CODE AS \"userId\",\n" +
            "                    SU.LAST_NAME AS \"username\",\n" +
            "                    OW.UUID AS \"orderWerksUuid\",\n" +
            "                    OW.SPMS_ID AS \"spmsId\",\n" +
            "                    OW.OA_BIANHAO AS \"oaBianhao\",\n" +
            "                    OW.STATUS AS \"statusOrderWerks\"\n" +
            "    FROM ORDER_WORKSHOP OWS\n" +
            "        LEFT JOIN ORDER_WORKSHOP_DTL OWSD ON OWS.UUID = OWSD.UUID\n" +
            "        LEFT JOIN ORDER_WERKS_DTL OWD ON OWSD.SPMS_WS_ID = OWD.SPMS_WS_ID AND OWSD.ROW_ID = OWD.ROW_ID_WS\n" +
            "        LEFT JOIN ORDER_WERKS OW ON OWD.UUID = OW.UUID\n" +
            "        LEFT JOIN SPMS_USER SU ON OWS.WORK_CODE = SU.WORK_CODE\n" +
            "    WHERE OWS.STATUS != '0'\n" +
            "      AND OWS.WORK_CODE = #{userId}\n" +
            " <when test='dto.werks!=null and dto.werks != \"\"'>AND OWS.WERKS = #{dto.werks} </when>\n" +
            " <when test='dto.workshopCode!=null and dto.workshopCode != \"\"'>AND OWS.WORKSHOP_CODE = #{dto.workshopCode} </when>\n" +
            " <when test='dto.spmsWsId!=null and dto.spmsWsId != \"\"'>AND OWS.SPMS_WS_ID = #{dto.spmsWsId} </when>\n" +
            " <when test='dto.urgency!=null and dto.urgency != \"\"'>AND OWS.URGENCY = #{dto.urgency} </when>\n" +
            " <when test='dto.status!=null and dto.status != \"\"'>AND OWS.STATUS = #{dto.status} </when>\n" +
            " <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(OWS.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd') </when>" +
            " <when test='dto.endDate!=null and dto.endDate != \"\"'> AND TO_DATE(OWS.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd') </when>" +
            "  ORDER BY TO_DATE(OWS.CREATE_DATE, 'yyyy-MM-dd') DESC, OWS.SPMS_WS_ID \n" +
            "    ) RE" +
            "</script>")
    /** 海外仓首页，车间请购单整单追溯查询 */
    List<Map<String, Object>> orderWorkshopTrace(IPage iPage,
                                                 @Param("dto") WelcomePageSearchDTO dto,
                                                 @Param("userId") String userId);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "    SELECT DISTINCT OW.UUID AS \"orderWerksUuid\",\n" +
            "                    OW.SPMS_ID AS \"spmsId\",\n" +
            "                    OW.WERKS AS \"werks\",\n" +
            "                    OW.SUBJECT AS \"subject\",\n" +
            "                    OW.APPLICATION_DATE AS \"applicationDate\",\n" +
            "                    OW.URGENCY AS \"urgency\",\n" +
            "                    OW.STATUS AS \"statusOrderWerks\",\n" +
            "                    OW.WORK_CODE AS \"userId\",\n" +
            "                    OW.OA_BIANHAO AS \"oaBianhao\",\n" +
            "                    SU.LAST_NAME AS \"username\"\n" +
            "    FROM ORDER_WERKS OW\n" +
            "        LEFT JOIN SPMS_USER SU ON OW.WORK_CODE = SU.WORK_CODE\n" +
            "    WHERE OW.STATUS != '0'\n" +
            " <when test='dto.werks!=null and dto.werks != \"\"'>AND OW.WERKS = #{dto.werks} </when>\n" +
            " <when test='dto.spmsId!=null and dto.spmsId != \"\"'>AND OW.SPMS_ID = #{dto.spmsId} </when>\n" +
            " <when test='dto.urgency!=null and dto.urgency != \"\"'>AND OW.URGENCY = #{dto.urgency} </when>\n" +
            " <when test='dto.status!=null and dto.status != \"\"'>AND OW.STATUS = #{dto.status} </when>\n" +
            " <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd') </when>" +
            " <when test='dto.endDate!=null and dto.endDate != \"\"'> AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd') </when>" +
            "    ORDER BY TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') DESC, OW.SPMS_ID\n" +
            "    ) RE" +
            "</script>")
    /** 海外仓首页，工厂请购单整单追溯查询 */
    List<Map<String, Object>> orderWerksTrace(IPage iPage, @Param("dto")WelcomePageWerksOrderDTO dto);

    // 以下注释部分为追溯方式：先按工厂请购单 uuid 获取工厂请购单明细行；再根据每一行明细行的 uuid + 行号，查询关联箱码及所有箱码当前位置；
    //@Select("SELECT OW.UUID AS \"orderWerksUuid\",\n" +
    //        "       OW.SPMS_ID AS \"spmsId\",\n" +
    //        "       OWD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
    //        "       OWS.UUID AS \"orderWorkshopUuid\",\n" +
    //        "       OWS.SPMS_WS_ID AS \"spmsWsId\",\n" +
    //        "       OWSD.ROW_ID AS \"rowIdWorkshop\",\n" +
    //        "       OWSD.MATERIAL_NO AS \"materialNo\",\n" +
    //        "       OWSD.REQUEST_QTY AS \"requestQty\",\n" +
    //        "       OWSD.APPROVAL_QTY AS \"approvalQty\",\n" +
    //        "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
    //        "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
    //        "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
    //        "       MS.UNIT_SALES AS \"unitSales\"\n" +
    //        "FROM ORDER_WERKS OW\n" +
    //        "    LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID\n" +
    //        "    LEFT JOIN ORDER_WORKSHOP_DTL OWSD ON OWD.SPMS_WS_ID = OWSD.SPMS_WS_ID AND OWD.ROW_ID_WS = OWSD.ROW_ID\n" +
    //        "    LEFT JOIN ORDER_WORKSHOP OWS ON OWSD.UUID = OWS.UUID\n" +
    //        "    LEFT JOIN MATERIAL_SAP MS ON OWSD.MATERIAL_NO = MS.MATERIAL_NO\n" +
    //        "WHERE OWSD.APPROVAL_QTY > 0 AND OW.UUID = #{orderWerksUuid}\n" +
    //        "ORDER BY OW.SPMS_ID DESC, TO_NUMBER(OWD.ROW_ID_WERKS) ")
    ///** 按工厂请购单uuid查询所有明细行 */
    //List<Map<String, Object>> orderWerksDtlList(@Param("orderWerksUuid") String orderWerksUuid);
    //
    //@Select("SELECT W.UUID AS \"orderWerksUuid\",\n" +
    //        "       W.SPMS_ID AS \"spmsId\",\n" +
    //        "       WD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
    //        "       SBND.BOX_NOTE AS \"boxNote\",\n" +
    //        "       SBND.ITEM AS \"boxNoteItem\",\n" +
    //        "       SBND.MATERIAL_NO AS \"materialNo\",\n" +
    //        "       SBND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
    //        "       BOX_LOG.WERKS AS \"werks\",\n" +
    //        "       BOX_LOG.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
    //        "       BOX_LOG.OPERATION_TYPE AS \"operationType\",\n" +
    //        "       (CASE WHEN SBND.BOX_NOTE IS NULL THEN '箱码待生成'\n" +
    //        "           WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '自管仓'\n" +
    //        "           WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '盘亏'\n" +
    //        "           WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '自管仓已发出'\n" +
    //        "           WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '海外仓已到货'\n" +
    //        "           ELSE '自管仓待到货' END) AS \"boxStatus\",\n" +
    //        "       BOX_LOG.CREATE_TIME AS \"createTime\" \n" +
    //        "FROM ORDER_WERKS_DTL WD\n" +
    //        "    LEFT JOIN ORDER_WERKS W ON WD.UUID = W.UUID\n" +
    //        "    LEFT JOIN SAP_BOX_NOTE_DTL SBND ON SBND.DEMAND_NO = W.SPMS_ID AND SBND.DEMAND_NO_ITEM = WD.ROW_ID_WERKS\n" +
    //        "    LEFT JOIN (\n" +
    //        "        SELECT RE.ID, L.WERKS, L.WHS_LOCATION_CODE, L.BOX_NOTE, L.DOCUMENT_TYPE,\n" +
    //        "               L.OPERATION_TYPE, L.COMMENTS, L.REMARK, L.CREATE_TIME\n" +
    //        "        FROM (\n" +
    //        "            SELECT MAX(LOG_AUTO_ID) AS \"ID\"\n" +
    //        "            FROM WHS_OPERATE_LOG WOL\n" +
    //        "            WHERE DOCUMENT_TYPE IN ('2', '3') AND BOX_NOTE IS NOT NULL GROUP BY BOX_NOTE\n" +
    //        "            ) RE LEFT JOIN WHS_OPERATE_LOG L ON RE.ID = L.LOG_AUTO_ID\n" +
    //        "        ORDER BY TO_NUMBER(L.OPERATION_TYPE) DESC\n" +
    //        "        ) BOX_LOG ON SBND.BOX_NOTE = BOX_LOG.BOX_NOTE\n" +
    //        "    LEFT JOIN SAP_BOX_NOTE BN ON SBND.BOX_NOTE = BN.BOX_NOTE\n" +
    //        "WHERE W.UUID = #{orderWerksUuid} AND WD.ROW_ID_WERKS = #{rowIdWerks}\n" +
    //        "ORDER BY W.SPMS_ID DESC, TO_NUMBER(WD.ROW_ID_WERKS), SBND.BOX_NOTE NULLS FIRST \n")
    ///** 按工厂请购单uuid 和 行号查询明细行关联所有箱码所在位置 */
    //List<Map<String, Object>> orderWerksBoxTraceList(@Param("orderWerksUuid") String orderWerksUuid, @Param("rowIdWerks") String rowIdWerks);

    @Select("SELECT W.UUID AS \"orderWerksUuid\",\n" +
            "       W.SPMS_ID AS \"spmsId\",\n" +
            "       WD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
            "       WSD.UUID AS \"orderWorkshopUuid\",\n" +
            "       WSD.ROW_ID AS \"rowIdWorkshop\",\n" +
            "       WSD.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "       WSD.REQUEST_QTY AS \"requestQty\",\n" +
            "       WSD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "       WSD.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "       MS.UNIT_SALES AS \"unitSales\",\n" +
            "       SBND.BOX_NOTE AS \"boxNote\",\n" +
            "       SBND.ITEM AS \"boxNoteItem\",\n" +
            "       SBND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "       BOX_LOG.WERKS AS \"werks\",\n" +
            "       BOX_LOG.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       BOX_LOG.OPERATION_TYPE AS \"operationType\",\n" +
            "       (CASE WHEN SBND.BOX_NOTE IS NULL THEN '1'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '3'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '4'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '5'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '6'\n" +
            "           ELSE '2' END) AS \"boxStatus\"," +
            "       (CASE WHEN SBND.BOX_NOTE IS NULL THEN '箱码待生成'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '自管仓'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '盘亏'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '自管仓已发出'\n" +
            "           WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '海外仓已到货'\n" +
            "           ELSE '自管仓待到货' END) AS \"boxStatusDesc\",\n" +
            "       BOX_LOG.CREATE_TIME AS \"createTime\"\n" +
            "FROM ORDER_WERKS_DTL WD\n" +
            "    LEFT JOIN ORDER_WERKS W ON WD.UUID = W.UUID\n" +
            "    LEFT JOIN ORDER_WORKSHOP_DTL WSD ON WD.SPMS_WS_ID = WSD.SPMS_WS_ID AND WSD.ROW_ID = WD.ROW_ID_WS\n" +
            "    LEFT JOIN SAP_BOX_NOTE_DTL SBND ON SBND.DEMAND_NO = W.SPMS_ID AND SBND.DEMAND_NO_ITEM = WD.ROW_ID_WERKS\n" +
            "    LEFT JOIN (\n" +
            "        SELECT RE.ID, L.WERKS, L.WHS_LOCATION_CODE, L.BOX_NOTE, L.DOCUMENT_TYPE,\n" +
            "               L.OPERATION_TYPE, L.COMMENTS, L.REMARK, L.CREATE_TIME\n" +
            "        FROM (\n" +
            "            SELECT MAX(LOG_AUTO_ID) AS \"ID\"\n" +
            "            FROM WHS_OPERATE_LOG WOL\n" +
            "            WHERE DOCUMENT_TYPE IN ('2', '3') AND BOX_NOTE IS NOT NULL GROUP BY BOX_NOTE\n" +
            "            ) RE LEFT JOIN WHS_OPERATE_LOG L ON RE.ID = L.LOG_AUTO_ID\n" +
            "        ORDER BY TO_NUMBER(L.OPERATION_TYPE) DESC\n" +
            "        ) BOX_LOG ON SBND.BOX_NOTE = BOX_LOG.BOX_NOTE\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON MS.MATERIAL_NO = WSD.MATERIAL_NO\n" +
            "WHERE W.UUID = #{orderWerksUuid}\n" +
            "ORDER BY W.SPMS_ID DESC, TO_NUMBER(WD.ROW_ID_WERKS), SBND.BOX_NOTE NULLS FIRST \n")
    /** 按工厂请购单uuid 和 行号查询明细行关联所有箱码所在位置 */
    List<Map<String, Object>> orderWerksBoxTraceListByUuid(@Param("orderWerksUuid") String orderWerksUuid);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RESULT.* FROM (\n" +
            "    SELECT OW.WERKS AS \"werks\",\n" +
            "           W.WERKS_NAME_ZH AS \"werksNameZh\",\n" +
            "           OWS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "           WS.WORKSHOP_NAME_ZH AS \"workshopNameZh\",\n" +
            "           OWS.WORK_CODE AS \"orderWorkshopUserid\",\n" +
            "           OWS.LAST_NAME AS \"orderWorkshopUsername\",\n" +
            "           OW.WORK_CODE AS \"orderWerksUserid\",\n" +
            "           OW.LAST_NAME AS \"orderWerksUsername\",\n" +
            "           OW.SPMS_ID AS \"orderWerksId\",\n" +
            "           OWD.ROW_ID_WERKS AS \"orderWerksRowId\",\n" +
            "           OW.OA_PROCESS_ID AS \"oaProcessId\",\n" +
            "           OW.APPLICATION_DATE AS \"orderWerksApplicationDate\",\n" +
            "           OW.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "           OW.URGENCY AS \"urgency\",\n" +
            "           OW.FIELD_FOR_OA AS \"orderWerksFieldForOa\",\n" +
            "           OW.SHIPPING_TYPE AS \"orderWerksShippingType\",\n" +
            "           OW.SHIPPING_FANGSHI AS \"orderWerksShippingFangshi\",\n" +
            "           OW.JUSTIFICATION AS \"orderWerksJustification\",\n" +
            "           OWS.SUBJECT AS \"orderWorkshopSubject\",\n" +
            "           OWS.SPMS_WS_ID AS \"orderWorkshopId\",\n" +
            "           OWSD.ROW_ID AS \"orderWorkshopRowId\",\n" +
            "           OWS.APPLICATION_DATE AS \"orderWorkshopApplicationDate\",\n" +
            "           OWSD.MATERIAL_NO AS \"materialNo\",\n" +
            "           SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "           SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "           OWSD.REQUEST_QTY AS \"requestQty\",\n" +
            "           OWSD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "           ZSPSS.STATUS AS \"prStatus\",\n" +
            "           ZSPSS.PRICE_REVIEW_TIME AS \"priceReviewTime\",\n" +
            "           ZSPSS.UNLOCK_REASON AS \"unlockReason\",\n" +
            "           ZSPSS.FBK29 AS \"fbk29\",\n" +
            "           ZSPSS.FBK32 AS \"fbk32\",\n" +
            "           ZSPSS.INTERNAL_ORDER_NO AS \"internalOrderNo\",\n" +
            "           ZSPSS.SRM_ORDER_NO AS \"srmOrderNo\",\n" +
            "           ZSPSS.SAP_ORDER_NO AS \"sapOrderNo\",\n" +
            "           ZSPSS.PO_CREATE_DATE AS \"poCreateDate\",\n" +
            "           SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "           NVL(SI.SAFETY_QTY_BASIC_UNIT, 0) AS \"safetyQtyBasicUnit\",\n" +
            "           NVL(WI.WERKS_INVENTORY, 0) AS \"werksInventory\",\n" +
            "           NVL(WAY.WAY_QUANTITY, 0) AS \"wayQuantity\",\n" +
            "           ZEGTI.PACKAGE_CODE AS \"boxNote\",\n" +
            "           ZEGTI.PACKAGE_POSNR AS \"boxNoteItem\",\n" +
            "           ZEGTI.STATSU_PKCODE AS \"boxStatus\",\n" +
            "           ZEGTI.MENGE AS \"domesticQtyReceived\",\n" +

            "           ZAITU.CABNO AS \"zaituCabno\",\n" +
            "           ZAITU.EBELN AS \"zaituEbeln\",\n" +
            "           ZAITU.EBELP AS \"zaituEbelp\",\n" +
            "           ZAITU.DGDAT AS \"zaituDgdat\",\n" +
            "           ZAITU.VBELN AS \"zaituVbeln\",\n" +
            "           ZAITU.NUMC AS \"zaituPosnr\",\n" +
            "           ZAITU.WAMNG AS \"zaituMenge\",\n" +
            "           ZAITU.WEMNG AS \"zaituWamng\",\n" +

            "           (CASE WHEN ZEGTI.STATSU_PKCODE = '1' THEN '采购在途'\n" +
            "               WHEN ZEGTI.STATSU_PKCODE = '2' THEN '国内在库'\n" +
            "               WHEN ZEGTI.STATSU_PKCODE = '3' THEN '海运在途'\n" +
            "               WHEN ZEGTI.STATSU_PKCODE = '4' THEN '海外在库'\n" +
            "               WHEN ZEGTI.STATSU_PKCODE = '5' THEN '不可用'\n" +
            "               WHEN ZEGTI.STATSU_PKCODE = '6' THEN '已删除'\n" +
            "               ELSE '' END) AS \"boxStatusDesc\",\n" +
            "           ZEGTI.VBELN AS \"deliveryNote\",\n" +
            "           (CASE WHEN ZEGTI.INBOUND_TIME IS NOT NULL\n" +
            "                      THEN TO_CHAR(TO_DATE(ZEGTI.INBOUND_TIME, 'yyyy-MM-dd'), 'yyyy-MM-dd')\n" +
            "               END) AS \"domesticArrivalDate\",\n" +
            "           ZEGTI.XDNUM AS \"packageList\",\n" +
            "           ZEGTI.ZPOSNR AS \"zposnr\",\n" +
            "           ZEGTI.GNSJSL AS \"gnsjsl\",\n" +
            "           (CASE WHEN ZEGTI.OUTBOUND_TIME IS NOT NULL\n" +
            "                      THEN ZEGTI.OUTBOUND_TIME  \n" +
            "               END) AS \"domesticDeliveryDate\",\n" +
            "           (CASE WHEN ZEGTI.OVERSEAS_TIME IS NOT NULL\n" +
            "                      THEN  ZEGTI.OVERSEAS_TIME \n" +
            "               END) AS \"overseaArrivalDate\",\n" +
            "           (CASE WHEN ZEGTI.OVERSEAS_TIME IS NOT NULL\n" +
            "                      THEN TO_DATE(ZEGTI.OVERSEAS_TIME , 'yyyy-MM-dd') -\n" +
            "                           TO_DATE(OWS.APPLICATION_DATE, 'yyyy-MM-dd')\n" +
            "               END) AS \"daysSpent\"\n" +
            "    FROM ORDER_WERKS OW\n" +
            "        LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID\n" +
            "        LEFT JOIN ORDER_WORKSHOP_DTL OWSD ON OWD.SPMS_WS_ID = OWSD.SPMS_WS_ID AND OWD.ROW_ID_WS = OWSD.ROW_ID\n" +
            "        LEFT JOIN ORDER_WORKSHOP OWS ON OWSD.UUID = OWS.UUID\n" +
            "        LEFT JOIN WERKS W ON OW.WERKS = W.WERKS\n" +
            "        LEFT JOIN WORKSHOP WS ON OW.WERKS = WS.WERKS AND OWS.WORKSHOP_CODE = WS.WORKSHOP_CODE\n" +
            "        LEFT JOIN MATERIAL_SAP SAP ON OWSD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "        LEFT JOIN Z_SPMS_PR_SRM_STATUS ZSPSS ON OWD.SPMS_ID = ZSPSS.PR_NUMBER AND  OWD.ROW_ID_WERKS = to_number(ZSPSS.PR_ITEM_NUMBER)  \n" +
            "        LEFT JOIN SAFETY_INVENTORY SI ON OW.WERKS = SI.WERKS AND OWSD.MATERIAL_NO = SI.MATERIAL_NO\n" +
            "        LEFT JOIN (\n" +
            "            SELECT WS.WERKS, WSI.MATERIAL_NO, SUM(WSI.QUANTITY) AS \"WERKS_INVENTORY\"\n" +
            "            FROM WHS_STORAGE_INVENTORY WSI\n" +
            "                LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "            GROUP BY WS.WERKS, WSI.MATERIAL_NO\n" +
            "            ) WI ON OW.WERKS = WI.WERKS AND OWSD.MATERIAL_NO = WI.MATERIAL_NO\n" +
            "    LEFT JOIN (\n" +
            "        SELECT SHWERKS, MATNR, SUM(WAMNG) AS \"WAY_QUANTITY\"\n" +
            "        FROM Z_SPMS_ZAITU \n" +
            "        GROUP BY SHWERKS, MATNR\n" +
            "    ) WAY ON OW.WERKS = WAY.SHWERKS AND OWSD.MATERIAL_NO = WAY.MATNR\n" +
            "        LEFT JOIN Z_EAM_GET_TRANSPORT_INFO ZEGTI ON OW.SPMS_ID = ZEGTI.DEMAND_NO AND OWD.ROW_ID_WERKS = ZEGTI.DEMAND_POSNR \n" +
            "        LEFT JOIN Z_SPMS_ZAITU ZAITU ON ZEGTI.PACKAGE_CODE = ZAITU.PACKAGE_CODE AND ZEGTI.MATNR = ZAITU.MATNR \n" +
            "    WHERE  OW.STATUS IN ('4','5') " +
            "    AND (ZSPSS.STATUS is null or ZSPSS.STATUS in('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','22','23','24')) \n" +
            "    <when test='dto.werks!=null and dto.werks != \"\"'>AND OW.WERKS = #{dto.werks} </when>\n" +
            "    <when test='dto.spmsId!=null and dto.spmsId != \"\"'>AND OW.SPMS_ID = #{dto.spmsId} </when>\n" +
            "    <when test='dto.materialNo!=null and dto.materialNo != \"\"'>AND OWSD.MATERIAL_NO = #{dto.materialNo} </when>\n" +
            "<when test='dto.materialName!=null'> AND " +
            "( " +
            " OWSD.MATERIAL_ZH_DESC LIKE '%'||#{dto.materialName}||'%' \n" +
            " OR OWSD.MATERIAL_EN_DESC LIKE '%'||#{dto.materialName}||'%' \n" +
            ")" +
            "</when> \n" +
            "    <when test='dto.beginDate!=null and dto.beginDate != \"\"'>AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>\n" +
            "    <when test='dto.endDate!=null and dto.endDate != \"\"'>AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
            "    ORDER BY OW.SPMS_ID, OWD.ROW_ID_WERKS \n" +
            "    ) RESULT " +
            "</script>")
    List<Map<String, Object>> getOrderKeyDates(@Param("dto") OverseaReport dto);
    
    
    @Select("<script>" +
    		"SELECT ROWNUM AS \"rowId\", RESULT.* FROM (\n" +
    		"    SELECT OWI.WHS_IN_NO AS \"spmsId\",\n" +
    		"    	OWI.WERKS AS \"werks\",\n" +
    		"    	OWI.WHS_LOCATION_CODE AS \"locationCode\",\n" +
    		"    	OWI.SPMS_STATUS AS \"spmsStatus\",\n" +
    		"    	(CASE WHEN OWI.SPMS_STATUS = '5' THEN '提交SAP失败' ELSE '待提交SAP' END) AS \"spmsStatusDesc\",\n" +
    		"    	'OWI' AS \"orderType\",\n" +
    		"       OWI.CREATE_DATE AS \"createDate\"\n" +
            "    FROM OVERSEA_WHS_IN OWI\n" +
            "    WHERE OWI.SPMS_STATUS IN ('5','3') \n" +
            "    	<when test='dto.spmsId !=null and dto.spmsId != \"\"'>AND OWI.WHS_IN_NO = #{dto.spmsId} </when>\n" +
            "    	<when test='dto.werks !=null and dto.werks != \"\"'>AND OWI.WERKS = #{dto.werks} </when>\n" +
            "    	<when test='dto.beginDate !=null and dto.beginDate != \"\"'>AND TO_DATE(OWI.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>\n" +
            "    	<when test='dto.endDate !=null and dto.endDate != \"\"'>AND TO_DATE(OWI.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
            "	 UNION ALL\n" +
            "    SELECT OICA.SPMS_ID AS \"spmsId\",\n" +
    		"    	OICA.WERKS AS \"werks\",\n" +
    		"    	OICA.WHS_LOCATION_CODE AS \"locationCode\",\n" +
    		"    	OICA.SPMS_STATUS AS \"spmsStatus\",\n" +
    		"    	(CASE WHEN OICA.SPMS_STATUS = '3' THEN '待提交OA' WHEN OICA.SPMS_STATUS = '5' THEN '待提交SAP' ELSE '提交SAP失败' END) AS \"spmsStatusDesc\",\n" +
    		"    	'OICA' AS \"orderType\",\n" +
    		"       (CASE WHEN OICA.CREATE_TIME IS NOT NULL THEN TO_CHAR(OICA.CREATE_TIME, 'yyyy-MM-dd') END) AS \"createDate\"\n" +
            "    FROM OVERSEA_INV_CHECK_ACTION OICA\n" +
            "    WHERE OICA.SPMS_STATUS IN ('3','5','6') \n" +
            "    	<when test='dto.spmsId !=null and dto.spmsId != \"\"'>AND OICA.SPMS_ID = #{dto.spmsId} </when>\n" +
            "    	<when test='dto.werks !=null and dto.werks != \"\"'>AND OICA.WERKS = #{dto.werks} </when>\n" +
            "    	<when test='dto.beginDate !=null and dto.beginDate != \"\"'>AND TO_CHAR(OICA.CREATE_TIME, 'yyyy-MM-dd') &gt;= #{dto.beginDate}</when>\n" +
            "    	<when test='dto.endDate !=null and dto.endDate != \"\"'>AND TO_CHAR(OICA.CREATE_TIME, 'yyyy-MM-dd') &lt;= #{dto.endDate}</when>\n" +
            "	 UNION ALL\n" +
            "	 SELECT OOT.SPMS_ID AS \"spmsId\",\n" +
    		"    	OOT.WERKS AS \"werks\",\n" +
    		"    	OOT.WHS_LOCATION_CODE AS \"locationCode\",\n" +
    		"    	OOT.SPMS_STATUS AS \"spmsStatus\",\n" +
    		"    	(CASE WHEN OOT.SPMS_STATUS = '4' THEN '待提交SAP' ELSE '提交SAP失败' END) AS \"spmsStatusDesc\",\n" +
    		"    	'OOT' AS \"orderType\",\n" +
    		"       OOT.CREATE_DATE AS \"createDate\"\n" +
            "    FROM OVERSEA_OTHER_TASK OOT\n" +
            "    WHERE OOT.SPMS_STATUS IN ('4','5')\n" +
            "    	<when test='dto.spmsId !=null and dto.spmsId != \"\"'>AND OOT.SPMS_ID = #{dto.spmsId} </when>\n" +
            "    	<when test='dto.werks !=null and dto.werks != \"\"'>AND OOT.WERKS = #{dto.werks} </when>\n" +
            "    	<when test='dto.beginDate !=null and dto.beginDate != \"\"'>AND TO_DATE(OOT.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>\n" +
            "    	<when test='dto.endDate !=null and dto.endDate != \"\"'>AND TO_DATE(OOT.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
            "	 UNION ALL\n" +
            "	 SELECT OWD.WHS_DUMP_NO AS \"spmsId\",\n" +
    		"    	OWD.WERKS_DUMP_IN AS \"werks\",\n" +
    		"    	OWD.WHS_LOCATION_CODE_DUMP_IN AS \"locationCode\",\n" +
    		"    	OWD.SPMS_STATUS AS \"spmsStatus\",\n" +
    		"    	(CASE WHEN OWD.SPMS_STATUS = '6' THEN '待提交SAP获取PO' WHEN OWD.SPMS_STATUS = '7' THEN '提交SAP获取PO失败' WHEN OWD.SPMS_STATUS = '8' THEN '待提交SAP获取DN' ELSE '提交SAP获取DN失败' END) AS \"spmsStatusDesc\",\n" +
    		"    	'OWD' AS \"orderType\",\n" +
    		"       OWD.CREATE_DATE AS \"createDate\"\n" +
            "    FROM OVERSEA_WHS_DUMP OWD\n" +
            "    WHERE OWD.SPMS_STATUS IN ('6','7','8','9')\n" +
            "    	<when test='dto.spmsId !=null and dto.spmsId != \"\"'>AND OWD.WHS_DUMP_NO = #{dto.spmsId} </when>\n" +
            "    	<when test='dto.werks !=null and dto.werks != \"\"'>AND OWD.WERKS_DUMP_IN = #{dto.werks} </when>\n" +
            "    	<when test='dto.beginDate !=null and dto.beginDate != \"\"'>AND TO_DATE(OWD.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>\n" +
            "    	<when test='dto.endDate !=null and dto.endDate != \"\"'>AND TO_DATE(OWD.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
            "	 UNION ALL\n" +
            "	 SELECT OWO.SPMS_ID AS \"spmsId\",\n" +
    		"    	OWO.WERKS AS \"werks\",\n" +
    		"    	OWO.WORKSHOP_CODE AS \"locationCode\",\n" +
    		"    	OWO.SPMS_STATUS AS \"spmsStatus\",\n" +
    		"    	(CASE WHEN OWO.SPMS_STATUS = '2' THEN '待提交SAP' ELSE '提交SAP失败' END) AS \"spmsStatusDesc\",\n" +
    		"    	'OWO' AS \"orderType\",\n" +
    		"       OWO.CREATE_DATE AS \"createDate\"\n" +
            "    FROM OVERSEA_WHS_OUT OWO\n" +
            "    WHERE OWO.SPMS_STATUS IN ('2','4')\n" +
            "    	<when test='dto.spmsId !=null and dto.spmsId != \"\"'>AND OWO.SPMS_ID = #{dto.spmsId} </when>\n" +
            "    	<when test='dto.werks !=null and dto.werks != \"\"'>AND OWO.WERKS = #{dto.werks} </when>\n" +
            "    	<when test='dto.beginDate !=null and dto.beginDate != \"\"'>AND TO_DATE(OWO.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>\n" +
            "    	<when test='dto.endDate !=null and dto.endDate != \"\"'>AND TO_DATE(OWO.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
            "	 UNION ALL\n" +
            "	 SELECT OD.SAP_DELIVERY_NOTE AS \"spmsId\",\n" +
    		"    	OD.WERKS_RECEIVE AS \"werks\",\n" +
    		"    	OD.WHS_LOCATION_CODE_RECEIVE AS \"locationCode\",\n" +
    		"    	OD.SPMS_STATUS AS \"spmsStatus\",\n" +
    		"    	(CASE WHEN OD.SPMS_STATUS = '3' THEN '待提交SAP' ELSE '提交SAP失败' END) AS \"spmsStatusDesc\",\n" +
    		"    	'OD' AS \"orderType\",\n" +
    		"       (CASE WHEN OD.CREATE_TIME IS NOT NULL THEN TO_CHAR(OD.CREATE_TIME, 'yyyy-MM-dd') END) AS \"createDate\"\n" +
            "    FROM OVERSEA_DN OD\n" +
            "    WHERE OD.SPMS_STATUS IN ('3','5','6')\n" +
            "    	<when test='dto.spmsId !=null and dto.spmsId != \"\"'>AND OD.SAP_DELIVERY_NOTE = #{dto.spmsId} </when>\n" +
            "    	<when test='dto.werks !=null and dto.werks != \"\"'>AND OD.WERKS_RECEIVE = #{dto.werks} </when>\n" +
            "    	<when test='dto.beginDate !=null and dto.beginDate != \"\"'>AND TO_CHAR(OD.CREATE_TIME, 'yyyy-MM-dd') &gt;= #{dto.beginDate}</when>\n" +
            "    	<when test='dto.endDate !=null and dto.endDate != \"\"'>AND TO_CHAR(OD.CREATE_TIME, 'yyyy-MM-dd') &lt;= #{dto.endDate}</when>\n" +
            "	 UNION ALL\n" +
            "	 SELECT OWM.WHS_MOVE_NO AS \"spmsId\",\n" +
    		"    	OWM.WERKS_MOVE_IN AS \"werks\",\n" +
    		"    	OWM.WHS_LOCATION_CODE_MOVE_IN AS \"locationCode\",\n" +
    		"    	OWM.SPMS_STATUS AS \"spmsStatus\",\n" +
    		"    	(CASE WHEN OWM.SPMS_STATUS = '2' THEN '待提交SAP' ELSE '提交SAP失败' END) AS \"spmsStatusDesc\",\n" +
    		"    	'OWM' AS \"orderType\",\n" +
    		"       OWM.CREATE_DATE AS \"createDate\"\n" +
            "    FROM OVERSEA_WHS_MOVE OWM\n" +
            "    WHERE OWM.SPMS_STATUS IN ('3','2')\n" +
            "    	<when test='dto.spmsId !=null and dto.spmsId != \"\"'>AND OWM.WHS_MOVE_NO = #{dto.spmsId} </when>\n" +
            "    	<when test='dto.werks !=null and dto.werks != \"\"'>AND OWM.WERKS_MOVE_IN = #{dto.werks} </when>\n" +
            "    	<when test='dto.beginDate !=null and dto.beginDate != \"\"'>AND TO_DATE(OWM.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd')</when>\n" +
            "    	<when test='dto.endDate !=null and dto.endDate != \"\"'>AND TO_DATE(OWM.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd')</when>\n" +
			") RESULT " +
    		"</script>")
	List<Map<String, Object>> getSendSapFailedPage(IPage iPage, @Param("dto")OverseaReport dto);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\"," +
            "		MP.MATERIAL_NO AS \"materialNo\",\n" +
            "       MP.PHOTOS AS \"photos\",\n" +
            "		(SELECT COUNT(*) FROM MATERIAL_PHOTOS WHERE PHOTOS IS NOT NULL OR PHOTOS != '') AS \"photosNum\"\n" +
            "FROM MATERIAL_PHOTOS MP\n" +
            "WHERE 1=1 \n" +
            " <when test='dto.materialNo!=null and dto.materialNo != \"\"'> AND MP.MATERIAL_NO = #{dto.materialNo}</when>" +
            "</script>")
    List<Map<String, Object>> getMaterialPhotosPage(IPage iPage, @Param("dto")OverseaConsumptionDTO dto);


    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\"," +
            "		MP.MATERIAL_NO AS \"materialNo\",\n" +
            "       MP.ATTACH_FILE AS \"photos\"\n" +
            "FROM MATERIAL_SAP MP\n" +
            "WHERE MP.ON_MALL = 'X' \n" +
            " <when test='dto.materialNo!=null and dto.materialNo != \"\"'> AND MP.MATERIAL_NO = #{dto.materialNo}</when>" +
            "</script>")
    List<Map<String, Object>> getMaterialAttachFilePage(IPage iPage, @Param("dto")OverseaConsumptionDTO dto);
    
    @Select("<script>" +
            "SELECT " +
            "		COUNT(*) AS \"materNum\"\n" +
            "FROM MATERIAL_SAP MS\n" +
            "WHERE MS.ON_MALL = 'X' \n" +
            "</script>")
	Map<String, Object> getAllMaterialPhotos(OverseaConsumptionDTO dto);


    @Select("<script>" +
            "SELECT " +
            "		COUNT(*) AS \"materNum\"\n" +
            "FROM MATERIAL_SAP MS\n" +
            "WHERE MS.ON_MALL = 'X' and (MS.ATTACH_FILE IS NOT NULL OR MS.ATTACH_FILE != '' ) \n" +
            "</script>")
    Map<String, Object> getAllPhotosCount(OverseaConsumptionDTO dto);
    
    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", DD.* FROM (\n" +
    		"       SELECT \n" +
            "			WSI.MATERIAL_NO AS \"materialNo\",\n" +
            "       	WS.WERKS AS \"werks\",\n" +
            "       	WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       	MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       	MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "       	SUM( NVL(WSI.QUANTITY, 0) ) AS \"quantity\",\n" +
            "       	SUM( NVL(WSI.AVAILABLE_QTY, 0) ) AS \"availableQty\"\n" +
            "		FROM WHS_STORAGE_INVENTORY WSI\n" +
            " 			LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            " 			LEFT JOIN MATERIAL_SAP MS ON MS.MATERIAL_NO = WSI.MATERIAL_NO\n" +
            "		WHERE 1=1 \n" +
            " 		<when test='dto.materialNo!=null and dto.materialNo != \"\"'> AND (WSI.MATERIAL_NO LIKE concat(concat('%',#{dto.materialNo}),'%') OR MS.MATERIAL_ZH_DESC LIKE concat(concat('%',#{dto.materialNo}),'%'))</when>" +
            " 		<when test='dto.werks!=null and dto.werks != \"\"'> AND WS.WERKS = #{dto.werks}</when>" +
            " 		<when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WS.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " 		GROUP BY\n" +
            " 		WSI.MATERIAL_NO,WS.WERKS,WS.WHS_LOCATION_CODE,MS.MATERIAL_ZH_DESC,MS.BASIC_UNIT \n" +
            "		HAVING SUM( NVL( WSI.AVAILABLE_QTY, 0 ) ) > 0 \n" +
            ") DD \n" +
            "</script>")
	List<Map<String, Object>> selectMaterialStockQtyDifferList(@Param("dto")OverseaConsumptionDTO dto);
    
    
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.* \n" +
            "FROM (\n" +
    		"       SELECT \n" +
			"    		ORH.WERKS AS \"werks\",\n" +
			"   		ORH.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"   		'领用出库' AS \"orderType\",\n" +
			"    		ORH.SPMS_ID AS \"spmsId\",\n" +
			"   		ORH.RELATE_DOCUMENT AS \"eamNumber\",\n" +
			"    		(CASE WHEN ORH.SPMS_STATUS ='3' THEN '审批通过' ELSE '已完成' END) AS \"spmsStatus\",\n" +
			"    		SHOP.WORKSHOP_NAME_ZH AS \"workshopName\",\n" +
			"    		SU1.LAST_NAME AS \"createUser\",\n" +
			"    		ORD.MATERIAL_NO AS \"materialNo\",\n" +
			"    		MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
			"    		ORD.APPLY_QTY_BASIC_UNIT AS \"applyQty\",\n" +
			"    		ORD.ACTUAL_QTY_BASIC_UNIT AS \"actualQty\",\n" +
			"    		OWOD.ACTUAL_QTY_BASIC_UNIT AS \"outboundQty\",\n" +
			"    		ORD.BASIC_UNIT AS \"basicUnit\",\n" +
			"    		ORD.UNIT_SALES AS \"unitSale\",\n" +
			"    		OWO.SPMS_ID AS \"outSpmsId\",\n" +
			"    		(CASE WHEN OWO.SPMS_STATUS = '1' THEN '出库中' WHEN OWO.SPMS_STATUS = '2' THEN '待上传SAP' WHEN OWO.SPMS_STATUS = '3' THEN '提交SAP成功' WHEN OWO.SPMS_STATUS = '4' THEN '提交SAP失败' WHEN OWO.SPMS_STATUS = '5' THEN '已撤销' ELSE '已关闭' END) AS \"outSpmsStatus\",\n" +
			"    		SU2.LAST_NAME AS \"outCreateUser\",\n" +
			"    		OWO.OA_NUMBER AS \"oaNumber\",\n" +
			"    		OWOD.LICENSE_NO AS \"licenseNo\",\n" +
			"    		OWO.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
			"    		NVL(OWC.COST_CENTER_NAME, OWO.COST_CENTER) AS \"costCenter\",\n" +
			"    		OWO.MOVEMENT_TYPE AS \"movementType\",\n" +
			"    		OWOD.PROJECT_OA_NUMBER AS \"projectOaNumber\",\n" +
			"    		OWO.CANCEL_REASON AS \"cancelReason\",\n" +
			"    		ORH.CREATE_TIME AS \"createDate\",\n" +
			"    		(CASE WHEN ORH.REQUESTNUM = 'EAM' THEN ORH.CREATE_TIME ELSE OOL.CREATE_TIME END) AS approvalDate,\n" +
			"    		OWO.CREATE_TIME AS \"outboundDate\",\n" +
			"    		(CASE WHEN OOL2.LOG_CONTENT_EN = 'Submit SAP Success.' THEN OOL2.CREATE_TIME ELSE NULL END) AS \"postingDate\",\n" +
			"    		(CASE WHEN OOL2.LOG_CONTENT = '已删除' THEN OOL2.CREATE_TIME ELSE NULL END) AS \"closedDate\"\n" +
			"    	FROM \n" +
			"    		OVERSEA_REQUISITION_DTL ORD \n" +
			"    		LEFT JOIN OVERSEA_REQUISITION ORH \n" +
			"    			ON ORD.UUID_REQUISITION = ORH.UUID_REQUISITION \n" +
			"    		LEFT JOIN MATERIAL_SAP MS \n" +
			"    			ON ORD.MATERIAL_NO = MS.MATERIAL_NO \n" +
			"    		LEFT JOIN OVERSEA_WHS_OUT OWO \n" +
			"    			ON ORH.SPMS_ID = OWO.RELATE_DOCUMENT \n" +
			"    		LEFT JOIN OVERSEA_WHS_OUT_DTL OWOD \n" +
			"    			ON ORD.ITEM = OWOD.RELATE_DOCUMENT_ITEM \n" +
			"    			AND ORH.SPMS_ID = OWOD.RELATE_DOCUMENT \n" +
			"    		LEFT JOIN WORKSHOP SHOP \n" +
			"    			ON ORH.WERKS = SHOP.WERKS \n" +
			"    			AND ORH.WORKSHOP_CODE = SHOP.WORKSHOP_CODE \n" +
			"    		LEFT JOIN SPMS_USER SU1  \n" +
			"    			ON SU1.WORK_CODE = ORH.CREATE_USERID \n" +
			"    		LEFT JOIN SPMS_USER SU2 \n" +
			"    			ON SU2.WORK_CODE = OWO.CREATE_USERID \n" +
			"    		LEFT JOIN OVERSEA_WERKS_COST OWC \n" +
			"    			ON OWC.WERKS = OWO.WERKS \n" +
			"    			AND OWC.WHS_LOCATION_CODE = OWO.WHS_LOCATION_CODE \n" +
			"    			AND OWC.COST_CENTER = OWO.COST_CENTER \n" +
			"    		LEFT JOIN ( \n" +
			"    			SELECT \n" +
			"    				PDD.DOCUMENT_NUMBER ,\n" +
			"    				PDD.CREATE_TIME \n" +
			"    			FROM ( \n" +
			"    				SELECT  \n" +
			"    					DOCUMENT_NUMBER , \n" +
			"    					CREATE_TIME , \n" +
			"    					row_number () over (partition BY DOCUMENT_NUMBER,LOG_CONTENT ORDER BY AUTO_ID_LOG DESC,CREATE_TIME DESC) rn \n" +
			"    				FROM OVERSEA_OPERATE_LOG \n" +
			"    				WHERE LOG_CONTENT = '审批通过' \n" +
			"    			) PDD \n" +
			"    			WHERE PDD.rn = 1 \n" +
			"    		) OOL \n" +
			"    			ON ORD.UUID_REQUISITION = OOL.DOCUMENT_NUMBER \n" +
			"    		LEFT JOIN ( \n" +
			"    			SELECT \n" +
			"    				PCC.DOCUMENT_NUMBER ,\n" +
			"    				PCC.CREATE_TIME ,\n" +
			"    				PCC.LOG_CONTENT_EN ,\n" +
			"    				PCC.LOG_CONTENT \n" +
			"    			FROM ( \n" +
			"    				SELECT  \n" +
			"    					DOCUMENT_NUMBER ,\n" +
			"    					LOG_CONTENT ,\n" +
			"    					LOG_CONTENT_EN ,\n" +
			"    					CREATE_TIME , \n" +
			"    					row_number () over (partition BY DOCUMENT_NUMBER,LOG_CONTENT ORDER BY AUTO_ID_LOG DESC,CREATE_TIME DESC) rn \n" +
			"    				FROM OVERSEA_OPERATE_LOG \n" +
			"    			) PCC \n" +
			"    			WHERE PCC.rn = 1 \n" +
			"    		) OOL2 \n" +
			"    			ON OWO.UUID_WHS_OUT = OOL2.DOCUMENT_NUMBER \n" +
            "		WHERE ORH.SPMS_STATUS IN ('3', '5') \n" +
            " 		<when test='werks != null and werks != \"\"'> AND ORH.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND ORH.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            ") DD \n" +
            "	WHERE 1=1 \n" +
            " 		<when test='beginDate!=null'><![CDATA[ AND DD.approvalDate >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND DD.approvalDate <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "</script>")
	List<Map<String, Object>> queryOverseaWhsOutByPage(Page<Map<String, Object>> pages, @Param("werks") String werks,
														@Param("whsLocationCode") String whsLocationCode, 
														@Param("beginDate") String beginDate, 
														@Param("endDate") String endDate);
    
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.* \n" +
            "FROM (\n" +
    		"       SELECT \n" +
			"    		ODND.WERKS_RECEIVE AS \"werks\",\n" +
			"   		ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCode\",\n" +
			"   		'海运收货' AS \"orderType\",\n" +
			"    		ODND.SAP_DELIVERY_NOTE AS \"sapDeliveyNote\",\n" +
			"    		ODND.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveyNoteItem\",\n" +
			"    		(CASE WHEN ODN.SPMS_STATUS = '1' THEN '收货中' WHEN ODN.SPMS_STATUS = '2' THEN '入库完成' ELSE '收货完成' END) AS \"spmsStatus\",\n" +
			"    		ODND.PURCHASE_NO AS \"purchaseNo\",\n" +
			"    		ODND.PURCHASE_NO_ITEM AS \"purchaseNoItem\",\n" +
			"    		ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
			"    		ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
			"    		OWI.WHS_IN_NO AS \"spmsId\",\n" +
			"    		(CASE WHEN OWI.SPMS_STATUS = '4' THEN '提交SAP成功' WHEN OWI.SPMS_STATUS = '3' THEN '审批通过' WHEN OWI.SPMS_STATUS = '5' THEN '提交SAP失败' ELSE NULL END) AS \"inSpmsStatus\",\n" +
			"    		OPLB.DEMAND_NO AS \"requirementNo\",\n" +
			"    		OPLB.DEMANDER AS \"demander\",\n" +
			"    		OPLB.BOX_NOTE AS \"boxNote\",\n" +
			"    		ODND.MATERIAL_NO AS \"materialNo\",\n" +
			"    		SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
			"    		ODND.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
			"    		RE.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\",\n" +
			"    		ODND.BASIC_UNIT AS \"basicUnit\",\n" +
			"    		ODND.RECEIVABLE_QTY_UNIT_SALES AS \"receivableQtyUnitSales\",\n" +
			"    		RE.ACTUAL_QTY_UNIT_SALES AS \"actualQtyUnitSales\",\n" +
			"    		ODND.UNIT_SALES AS \"unitSale\",\n" +
			"    		NULL AS \"deliveryDate\",\n" +
			"    		NULL AS \"arrivalTime\",\n" +
			"    		OWI.CREATE_TIME AS \"inWhsCreateTime\",\n" +
			"    		NULL AS \"approvalDate\",\n" +
			"    		(CASE WHEN OWI.SPMS_STATUS = '4' THEN OOL.CREATE_TIME ELSE NULL END) AS \"inWhsEndTime\",\n" +
			"    		NULL AS \"oaApprovalDate\",\n" +
			"    		(CASE WHEN OOL2.LOG_CONTENT_EN = 'Undercharge Submit to SAP Successfully.' THEN OOL2.CREATE_TIME ELSE NULL END) AS \"underchargePostingDate\",\n" +
			"    		(CASE WHEN OOL2.LOG_CONTENT_EN = 'Overcharge Submit to SAP Successfully.' THEN OOL2.CREATE_TIME ELSE NULL END) AS \"overchargePostingDate\",\n" +
			"    		(CASE WHEN OOL2.LOG_CONTENT = '收货完成' THEN OOL2.CREATE_TIME ELSE NULL END) AS \"receivingDate\",\n" +
			"    		ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
			"    		ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
			"    		(CASE WHEN OOL2.LOG_CONTENT_EN = 'Overcharge Submit to SAP Successfully.' THEN ODN.SAP_VOUCHER_NUMBER ELSE NULL END) AS \"overVoucherNumber\",\n" +
			"    		SU.LAST_NAME AS \"createUser\"\n" +
			"    	FROM \n" +
			"    		OVERSEA_DELIVERY_NOTE_DTL ODND \n" +
			"    		LEFT JOIN OVERSEA_DELIVERY_NOTE ODN \n" +
			"    			ON ODND.SAP_DELIVERY_NOTE = ODN.SAP_DELIVERY_NOTE \n" +
			"    			AND ODND.WERKS_RECEIVE = ODN.WERKS_RECEIVE \n" +
			"    		LEFT JOIN OVERSEA_WHS_IN OWI \n" +
			"    			ON ODN.SAP_DELIVERY_NOTE = OWI.SAP_DELIVERY_NOTE \n" +
			"    			AND ODN.WERKS_RECEIVE = OWI.WERKS \n" +
			"				AND OWI.SPMS_STATUS = '4' \n" +
			"			LEFT JOIN OVERSEA_PACKAGE_LIST_DTL OPLD \n" +
			"				ON ODND.SAP_DELIVERY_NOTE = OPLD.SAP_DELIVERY_NOTE  \n" +
			"				AND ODND.PURCHASE_NO = OPLD.PURCHASE_NO \n" +
			"				AND ODND.PURCHASE_NO_ITEM = OPLD.PURCHASE_NO_ITEM \n" +
			"				AND ODND.SAP_DELIVERY_NOTE_ITEM = OPLD.SAP_DELIVERY_NOTE_ITEM \n" +
			"			LEFT JOIN OVERSEA_PACKAGE_LIST_BOX OPLB \n" +
			"				ON OPLD.SAP_PACKAGE_LIST = OPLB.SAP_PACKAGE_LIST \n" +
			"				AND OPLD.SAP_PACKAGE_LIST_ITEM = OPLB.SAP_PACKAGE_LIST_ITEM \n" +
			"				AND OPLD.BOX_NOTE_ITEM = OPLB.BOX_NOTE_ITEM \n" +
			"				AND OPLD.BOX_NOTE = OPLB.BOX_NOTE \n" +
			"				AND OPLD.SAP_DELIVERY_NOTE = OPLB.SAP_DELIVERY_NOTE \n" +
			"			LEFT JOIN MATERIAL_SAP SAP \n" +
			"				ON ODND.MATERIAL_NO = SAP.MATERIAL_NO \n" +
			"			LEFT JOIN ( \n" +
			"				SELECT OWI.SAP_DELIVERY_NOTE, \n" +
			"					OWID.SAP_DELIVERY_NOTE_ITEM, \n" +
			"					OWID.MATERIAL_NO,\n" +
			"					OWID.UUID_WHS_IN,\n" +
			"					SUM(NVL(OWID.ACTUAL_QTY_BASIC_UNIT, 0)) AS ACTUAL_QTY_BASIC_UNIT,\n" +
			"					SUM(NVL(OWID.ACTUAL_QTY_UNIT_SALES, 0)) AS ACTUAL_QTY_UNIT_SALES \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 = '4' \n" +
			"				GROUP BY OWID.UUID_WHS_IN, OWI.SAP_DELIVERY_NOTE, OWID.SAP_DELIVERY_NOTE_ITEM, OWID.MATERIAL_NO \n" +
			"			) RE \n" +
			"				ON RE.SAP_DELIVERY_NOTE = ODND.SAP_DELIVERY_NOTE  \n" +
			"				AND RE.SAP_DELIVERY_NOTE_ITEM = ODND.SAP_DELIVERY_NOTE_ITEM \n" +
			"				AND RE.UUID_WHS_IN = OWI.UUID_WHS_IN \n" +
			"			LEFT JOIN ( \n" +
			"				SELECT  \n" +
			"					PDD.DOCUMENT_NUMBER , \n" +
			"					PDD.CREATE_TIME \n" +
			"				FROM ( \n" +
			"					SELECT \n" +
			"						DOCUMENT_NUMBER , \n" +
			"						CREATE_TIME , \n" +
			"						row_number () over (partition BY DOCUMENT_NUMBER,LOG_CONTENT ORDER BY AUTO_ID_LOG DESC,CREATE_TIME DESC) rn \n" +
			"					FROM OVERSEA_OPERATE_LOG \n" +
			"					WHERE LOG_CONTENT = 'Warehousing Complete.' \n" +
			"				) PDD \n" +
			"				WHERE PDD.rn = 1 \n" +
			"			) OOL \n" +
			"				ON OWI.UUID_WHS_IN = OOL.DOCUMENT_NUMBER \n" +
			"			LEFT JOIN (\n" +
			"				SELECT \n" +
			"					PCC.DOCUMENT_NUMBER ,\n" +
			"					PCC.CREATE_TIME ,\n" +
			"					PCC.LOG_CONTENT_EN ,\n" +
			"					PCC.LOG_CONTENT\n" +
			"				FROM ( \n" +
			"					SELECT  \n" +
			"						DOCUMENT_NUMBER ,\n" +
			"						LOG_CONTENT ,\n" +
			"						LOG_CONTENT_EN ,\n" +
			"						CREATE_TIME , \n" +
			"						row_number () over (partition BY DOCUMENT_NUMBER,LOG_CONTENT ORDER BY AUTO_ID_LOG DESC,CREATE_TIME DESC) rn \n" +
			"					FROM OVERSEA_OPERATE_LOG \n" +
			"					WHERE (LOG_CONTENT = '收货完成' OR LOG_CONTENT_EN = 'Undercharge Submit to SAP Successfully.' OR LOG_CONTENT_EN = 'Overcharge Submit to SAP Successfully.') \n" +
			"				) PCC \n" +
			"				WHERE PCC.rn = 1 \n" +
			"			) OOL2 \n" +
			"				ON ODND.SAP_DELIVERY_NOTE = OOL2.DOCUMENT_NUMBER \n" +
			"			LEFT JOIN SPMS_USER SU \n" +
			"				ON SU.WORK_CODE = OWI.CREATE_USERID \n" +
            "		WHERE ODN.SPMS_STATUS IN ('1','2','11') \n" +
			"		AND (#{documentType} = '1' OR #{documentType} = '0') \n" +
            " 		<when test='werks != null and werks != \"\"'> AND ODND.WERKS_RECEIVE = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND (OOL2.LOG_CONTENT ='收货完成' AND OOL2.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')) ]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND (OOL2.LOG_CONTENT ='收货完成' AND OOL2.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd')) ]]></when>" +
            "		UNION ALL \n" +
	        "   	SELECT \n" +
		    "    		ODD.WERKS_RECEIVE AS \"werks\",\n" +
			"       	ODD.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCode\",\n" +
			"       	(CASE WHEN OD.BUSINESS_TYPE = '01' THEN '空运人带' WHEN OD.BUSINESS_TYPE = '02' THEN '本地采购' WHEN OD.BUSINESS_TYPE = '03' THEN '海外转储' ELSE NULL END) AS \"orderType\",\n" +
			"        	OD.SAP_DELIVERY_NOTE AS \"sapDeliveyNote\",\n" +
			"        	ODD.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveyNoteItem\",\n" +
			"        	'收货完成' AS \"spmsStatus\",\n" +
			"        	ODD.SAP_PURCHASE_ORDER AS \"purchaseNo\",\n" +
			"        	ODD.SAP_PURCHASE_ORDER_ITEM AS \"purchaseNoItem\",\n" +
			"       	NULL AS \"sapPackageList\",\n" +
			"        	NULL AS \"cabinetNumber\",\n" +
			"        	OOBD.SPMS_ID AS \"spmsId\",\n" +
			"       	(CASE WHEN OOBD.SPMS_ID IS NOT NULL THEN '提交SAP成功' ELSE NULL END) AS \"inSpmsStatus\",\n" +
			"        	NULL AS \"requirementNo\",\n" +
			"        	NULL AS \"demander\",\n" +
			"        	NULL AS \"boxNote\",\n" +
			"        	ODD.MATERIAL_NO AS \"materialNo\",\n" +
			"        	MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
			"        	ODD.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
			"        	(CASE WHEN OD.BUSINESS_TYPE = '02' THEN OOBD.ACTUAL_QUANTITY ELSE ODD.ACTUAL_QTY_BASIC_UNIT END) AS \"actualQtyBasicUnit\",\n" +
			"        	ODD.BASIC_UNIT AS \"basicUnit\",\n" +
			"        	ODD.RECEIVABLE_QTY_UNIT_SALES AS \"receivableQtyUnitSales\",\n" +
			"        	(CASE WHEN OD.BUSINESS_TYPE = '02' THEN OOBD.SALES_QUANTITY ELSE ODD.ACTUAL_QTY_UNIT_SALES END) AS \"actualQtyUnitSales\",\n" +
			"        	ODD.UNIT_SALES AS \"unitSale\",\n" +
			"        	OD.DELIVERY_DATE AS \"deliveryDate\",\n" +
			"        	OD.ARRIVAL_DATE AS \"arrivalTime\",\n" +
			"        	(CASE WHEN OD.BUSINESS_TYPE = '02' THEN OOBD.CREATE_TIME ELSE OD.CREATE_TIME END) AS \"inWhsCreateTime\",\n" +
			"        	NULL AS \"approvalDate\",\n" +
			"        	(CASE WHEN OD.BUSINESS_TYPE = '02' THEN OOBD.UPDATE_TIME ELSE OD.UPDATE_TIME END) AS \"inWhsEndTime\",\n" +
			"       	NULL AS \"oaApprovalDate\",\n" +
			"        	(CASE WHEN OD.BUSINESS_TYPE = '02' THEN NULL WHEN OOL2.LOG_CONTENT_EN = 'Undercharge Submit to SAP Successfully.' THEN OOL2.CREATE_TIME ELSE NULL END) AS \"underchargePostingDate\",\n" +
			"        	NULL AS \"overchargePostingDate\",\n" +
			"        	(CASE WHEN OOL2.LOG_CONTENT = '收货完成' THEN OOL2.CREATE_TIME ELSE NULL END) AS \"receivingDate\",\n" +
			"        	(CASE WHEN OD.BUSINESS_TYPE = '02' THEN OOBD.SAP_VOUCHER_NUMBER ELSE OD.SAP_VOUCHER_NUMBER END) AS \"sapVoucherNumber\",\n" +
			"        	(CASE WHEN OD.BUSINESS_TYPE = '02' THEN NULL ELSE OD.UNDER_VOUCHER_NUMBER END) AS \"underVoucherNumber\",\n" +
			"        	(CASE WHEN OD.BUSINESS_TYPE = '02' THEN NULL ELSE NULL END) AS \"overVoucherNumber\",\n" +
			"        	NVL(SU1.LAST_NAME, SU2.LAST_NAME) AS \"createUser\"\n" +
			"        FROM \n" +
			"        	OVERSEA_DN_DTL ODD \n" +
			"        LEFT JOIN OVERSEA_DN OD \n" +
			"        	ON ODD.SAP_DELIVERY_NOTE = OD.SAP_DELIVERY_NOTE \n" +
			"        LEFT JOIN MATERIAL_SAP MS \n" +
			"       	ON ODD.MATERIAL_NO = MS.MATERIAL_NO \n" +
			"        LEFT JOIN ( \n" +
			"        	SELECT \n" +
			"        			OOBR.SPMS_ID, \n" +
			"        			OOBR.SAP_VOUCHER_NUMBER, \n" +
			"        			OOBRD.SAP_DELIVERY_NOTE_RELATION, \n" +
			"        			OOBRD.ITEM_RELATION, \n" +
			"        			OOBR.CREATE_TIME, \n" +
			"        			OOBR.UPDATE_TIME, \n" +
			"        			OOBR.UPDATE_USERID, \n" +
			"        			SUM(OOBRD.ACTUAL_QUANTITY) AS ACTUAL_QUANTITY, \n" +
			"        			SUM(OOBRD.ACTUAL_QUANTITY*NVL(MMS.UMREN, 1)/NVL(MMS.UMREZ, 1)) AS SALES_QUANTITY \n" +
			"        	FROM OVERSEA_OTHER_BATCH_RECEIPT_DTL OOBRD \n" +
			"        	LEFT JOIN OVERSEA_OTHER_BATCH_RECEIPT OOBR \n" +
			"        		ON OOBRD.SPMS_ID = OOBR.SPMS_ID \n" +
			"        	LEFT JOIN MATERIAL_MARM_SAP MMS \n" +
			"        		ON MMS.MATERIAL_NO = OOBRD.MATERIAL_NO \n" +
			"        		AND MMS.MEINH = OOBRD.UNIT_SALES \n" +
			"        	WHERE OOBR.SPMS_STATUS = '5' \n" +
			"        	GROUP BY OOBR.SPMS_ID,OOBR.SAP_VOUCHER_NUMBER,OOBRD.SAP_DELIVERY_NOTE_RELATION,OOBRD.ITEM_RELATION,OOBR.CREATE_TIME,OOBR.UPDATE_TIME,OOBR.UPDATE_USERID \n" +
			"        ) OOBD \n" +
			"        	ON OOBD.SAP_DELIVERY_NOTE_RELATION = ODD.SAP_DELIVERY_NOTE \n" +
			"        	AND OOBD.ITEM_RELATION = ODD.SAP_DELIVERY_NOTE_ITEM \n" +
			"        LEFT JOIN ( \n" +
			"        	SELECT \n" +
			"        		PCC.DOCUMENT_NUMBER ,\n" +
			"        		PCC.CREATE_TIME ,\n" +
			"        		PCC.LOG_CONTENT_EN ,\n" +
			"        		PCC.LOG_CONTENT,\n" +
			"        		PCC.USER_ID \n" +
			"        	FROM ( \n" +
			"        		SELECT \n" +
			"        			DOCUMENT_NUMBER , \n" +
			"        			LOG_CONTENT ,\n" +
			"        			LOG_CONTENT_EN ,\n" +
			"        			CREATE_TIME ,\n" +
			"        			USER_ID,\n" +
			"        			row_number () over (partition BY DOCUMENT_NUMBER,LOG_CONTENT ORDER BY AUTO_ID_LOG DESC,CREATE_TIME DESC) rn \n" +
			"        		FROM OVERSEA_OPERATE_LOG \n" +
			"        		WHERE (LOG_CONTENT = '收货完成' OR LOG_CONTENT_EN = 'Undercharge Submit to SAP Successfully.') \n" +
			"        	) PCC \n" +
			"        	WHERE PCC.rn = 1 \n" +
			"        ) OOL2 ON OD.SAP_DELIVERY_NOTE = OOL2.DOCUMENT_NUMBER \n" +
			"        LEFT JOIN SPMS_USER SU1 \n" +
			"        	ON SU1.WORK_CODE = OOL2.USER_ID \n" +
			"        LEFT JOIN SPMS_USER SU2 \n" +
			"        	ON SU2.WORK_CODE = OOBD.UPDATE_USERID \n" +
			"        WHERE OD.SPMS_STATUS = '9' \n" +									
					/* documentType=1是查询海运收货的数据，不查海外其他收货的，所以这里BUSINESS_TYPE=XX */
            " 		<when test='documentType != null and documentType != \"0\" and documentType != \"2\" and documentType != \"3\" and documentType != \"4\"'> AND OD.BUSINESS_TYPE = 'XX'</when>" +
            " 		<when test='documentType != null and documentType == \"2\"'> AND OD.BUSINESS_TYPE = '02'</when>" +
            " 		<when test='documentType != null and documentType == \"3\"'> AND OD.BUSINESS_TYPE = '01'</when>" +
            " 		<when test='documentType != null and documentType == \"4\"'> AND OD.BUSINESS_TYPE = '03'</when>" +
            " 		<when test='documentType != null and documentType == \"0\"'> AND OD.BUSINESS_TYPE IN ('01','02','03')</when>" +
            " 		<when test='werks != null and werks != \"\"'> AND ODD.WERKS_RECEIVE = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND ODD.WHS_LOCATION_CODE_RECEIVE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND (OOL2.LOG_CONTENT ='收货完成' AND OOL2.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')) ]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND (OOL2.LOG_CONTENT ='收货完成' AND OOL2.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd')) ]]></when>" +
            ") DD \n" +
            "</script>")
	List<Map<String, Object>> queryOverseaDeliveryNoteByPage(Page<Map<String, Object>> pages, @Param("werks") String werks,
																@Param("whsLocationCode") String whsLocationCode, 
																@Param("beginDate") String beginDate, 
																@Param("endDate") String endDate,
																@Param("documentType") String documentType);
    
    
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.* \n" +
            "FROM (\n" +
    		"       SELECT \n" +
			"    		SDNBR.WERKS AS \"werks\",\n" +
			"   		SDNBR.LOCATION_CODE AS \"whsLocationCode\",\n" +
			"   		'国内仓收货入库' AS \"orderType\",\n" +
			"    		SDNBR.SPMS_ID AS \"spmsId\",\n" +
			"    		SDNBR.PDA_SN AS \"pdaSN\",\n" +
			"    		SDNBR.PDA_TIME AS \"pdaTime\",\n" +
			"    		'提交SAP成功' AS \"spmsStatus\",\n" +
			"    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
			"    	FROM \n" +
			"    		SAP_DELIVERY_NOTE_BATCH_RECEIPT SDNBR \n" +
            "		WHERE SDNBR.SPMS_STATUS = '3' \n" +
            " 		<when test='werks != null and werks != \"\"'> AND SDNBR.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND SDNBR.LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND TO_DATE(SDNBR.PDA_TIME, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND TO_DATE(SDNBR.PDA_TIME, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
			"    		WO.WERKS AS \"werks\",\n" +
			"   		WO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"   		'国内仓装柜出库' AS \"orderType\",\n" +
			"    		WO.WHS_OUT_NO AS \"spmsId\",\n" +
			"    		WO.PDA_SN AS \"pdaSN\",\n" +
			"    		WO.PDA_TIME AS \"pdaTime\",\n" +
			"    		'提交SAP成功' AS \"spmsStatus\",\n" +
			"    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
			"    	FROM \n" +
			"    		WHS_OUT WO \n" +
            "		WHERE WO.TASK_STATUS = '4' \n" +
            " 		<when test='werks != null and werks != \"\"'> AND WO.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND WO.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND TO_DATE(WO.PDA_TIME, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND TO_DATE(WO.PDA_TIME, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            ") DD \n" +
            "</script>")
	List<Map<String, Object>> queryPdaCoverageRateByPage(Page<Map<String, Object>> pages, 
														@Param("werks") String werks,
														@Param("whsLocationCode") String whsLocationCode, 
														@Param("beginDate") String beginDate, 
														@Param("endDate") String endDate);
    
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.WERKS AS \"werks\", \n" +
    		"	DD.WHSLOCATIONCODE AS \"whsLocationCode\", \n" +
    		"	COUNT(1) AS \"allCount\" \n" +
            "FROM (\n" +
    		"       SELECT \n" +
			"    		SDNBR.WERKS AS WERKS,\n" +
			"   		SDNBR.LOCATION_CODE AS WHSLOCATIONCODE \n" +
			"    	FROM \n" +
			"    		SAP_DELIVERY_NOTE_BATCH_RECEIPT SDNBR \n" +
            "		WHERE SDNBR.SPMS_STATUS = '3' \n" +
            " 		<when test='werks != null and werks != \"\"'> AND SDNBR.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND SDNBR.LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND TO_DATE(SDNBR.PDA_TIME, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND TO_DATE(SDNBR.PDA_TIME, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
			"    		WO.WERKS AS WERKS,\n" +
			"   		WO.WHS_LOCATION_CODE AS WHSLOCATIONCODE \n" +
			"    	FROM \n" +
			"    		WHS_OUT WO \n" +
            "		WHERE WO.TASK_STATUS = '4' \n" +
            " 		<when test='werks != null and werks != \"\"'> AND WO.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND WO.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND TO_DATE(WO.PDA_TIME, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND TO_DATE(WO.PDA_TIME, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            ") DD \n" +
            "GROUP BY DD.WERKS, DD.WHSLOCATIONCODE \n" +
            "</script>")
    List<Map<String, Object>> queryPdaCoverageRateAllCount(@Param("werks") String werks, 
									@Param("whsLocationCode") String whsLocationCode, 
									@Param("beginDate") String beginDate, 
									@Param("endDate") String endDate);
    
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.WERKS AS \"werks\", \n" +
    		"	DD.WHSLOCATIONCODE AS \"whsLocationCode\", \n" +
    		"	COUNT(1) AS \"pdaCount\" \n" +
            "FROM (\n" +
    		"       SELECT \n" +
			"    		SDNBR.WERKS AS WERKS,\n" +
			"   		SDNBR.LOCATION_CODE AS WHSLOCATIONCODE \n" +
			"    	FROM \n" +
			"    		SAP_DELIVERY_NOTE_BATCH_RECEIPT SDNBR \n" +
            "		WHERE SDNBR.SPMS_STATUS = '3' \n" +
			"		AND SDNBR.PDA_SN IS NOT NULL \n" +
            " 		<when test='werks != null and werks != \"\"'> AND SDNBR.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND SDNBR.LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND TO_DATE(SDNBR.PDA_TIME, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND TO_DATE(SDNBR.PDA_TIME, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
			"    		WO.WERKS AS WERKS,\n" +
			"   		WO.WHS_LOCATION_CODE AS WHSLOCATIONCODE \n" +
			"    	FROM \n" +
			"    		WHS_OUT WO \n" +
            "		WHERE WO.TASK_STATUS = '4' \n" +
            "		AND WO.PDA_SN IS NOT NULL \n" +
            " 		<when test='werks != null and werks != \"\"'> AND WO.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND WO.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND TO_DATE(WO.PDA_TIME, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND TO_DATE(WO.PDA_TIME, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            ") DD \n" +
            "GROUP BY DD.WERKS, DD.WHSLOCATIONCODE \n" +
            "</script>")
	List<Map<String, Object>> queryPdaCoverageRatePdaCount(@Param("werks") String werks, 
									@Param("whsLocationCode") String whsLocationCode, 
									@Param("beginDate") String beginDate, 
									@Param("endDate") String endDate);
    
    
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.* \n" +
            "FROM (\n" +
    		"       SELECT \n" +
			"    		OWI.WERKS AS \"werks\",\n" +
			"   		OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"   		'海运入库' AS \"orderType\",\n" +
			"    		OWI.WHS_IN_NO AS \"spmsId\",\n" +
			"    		'提交SAP失败' AS \"spmsStatus\",\n" +
			"    		TO_CHAR(OWI.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
			"    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
			"    	FROM \n" +
			"    		OVERSEA_WHS_IN OWI \n" +
            "		WHERE OWI.SPMS_STATUS = '5' \n" +
			"		AND ('0' = #{documentType} OR '1' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWI.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWI.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWI.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWI.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
			"    		OOBR.WERKS AS \"werks\",\n" +
			"   		OOBR.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"   		'海外本地采购' AS \"orderType\",\n" +
			"    		OOBR.SPMS_ID AS \"spmsId\",\n" +
			"    		'提交SAP失败' AS \"spmsStatus\",\n" +
			"    		TO_CHAR(OOBR.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
			"    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
			"    	FROM \n" +
			"    		OVERSEA_OTHER_BATCH_RECEIPT OOBR \n" +
            "		WHERE OOBR.SPMS_STATUS = '4' \n" +
            "		AND ('0' = #{documentType} OR '2' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OOBR.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OOBR.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OOBR.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OOBR.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OD.WERKS_RECEIVE AS \"werks\",\n" +
            "   		OD.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCode\",\n" +
            "   		(CASE WHEN OD.BUSINESS_TYPE = '01' THEN '空运人带' WHEN OD.BUSINESS_TYPE = '03' THEN '海外转储' ELSE '海外本地采购' END) AS \"orderType\",\n" +
            "    		OD.SAP_DELIVERY_NOTE AS \"spmsId\",\n" +
            "    		(CASE WHEN OD.SPMS_STATUS = '5' THEN '按应收提交SAP失败' WHEN OD.SPMS_STATUS = '6' THEN '收货少收提交SAP失败' ELSE NULL END) AS \"spmsStatus\",\n" +
            "    		TO_CHAR(OD.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
            "    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_DN OD \n" +
            "		WHERE OD.SPMS_STATUS IN ('5','6') \n" +
            " 		<when test='documentType != null and documentType != \"0\" and documentType != \"3\" and documentType != \"4\"'> AND OD.BUSINESS_TYPE = 'XX'</when>" +
            " 		<when test='documentType != null and documentType == \"3\"'> AND OD.BUSINESS_TYPE = '01'</when>" +
            " 		<when test='documentType != null and documentType == \"4\"'> AND OD.BUSINESS_TYPE = '03'</when>" +
            " 		<when test='documentType != null and documentType == \"0\"'> AND OD.BUSINESS_TYPE IN ('01','03')</when>" +
            " 		<when test='werks != null and werks != \"\"'> AND OD.WERKS_RECEIVE = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OD.WHS_LOCATION_CODE_RECEIVE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OD.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OD.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OICA.WERKS AS \"werks\",\n" +
            "   		OICA.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "   		'盘亏盘盈' AS \"orderType\",\n" +
            "    		OICA.SPMS_ID AS \"spmsId\",\n" +
            "    		(CASE WHEN OICA.SPMS_STATUS = '3' THEN '提交OA失败' WHEN OICA.SPMS_STATUS = '6' THEN '提交SAP失败' ELSE NULL END) AS \"spmsStatus\",\n" +
            "    		TO_CHAR(OICA.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
            "    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_INV_CHECK_ACTION OICA \n" +
            "		WHERE OICA.SPMS_STATUS IN ('3','6') \n" +
            "		AND ('0' = #{documentType} OR '5' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OICA.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OICA.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OICA.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OICA.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OWO.WERKS AS \"werks\",\n" +
            "   		OWO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "   		'仓库出库' AS \"orderType\",\n" +
            "    		OWO.SPMS_ID AS \"spmsId\",\n" +
            "    		(CASE WHEN OWO.SPMS_STATUS = '2' THEN '提交OA失败' WHEN OWO.SPMS_STATUS = '4' THEN '提交SAP失败' ELSE NULL END) AS \"spmsStatus\",\n" +
            "    		TO_CHAR(OWO.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
            "    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_OUT OWO \n" +
            "		WHERE OWO.SPMS_STATUS IN ('2','4') \n" +
            "		AND ('0' = #{documentType} OR '6' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWO.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWO.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWO.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWO.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OWM.WERKS_MOVE_IN AS \"werks\",\n" +
            "   		OWM.WHS_LOCATION_CODE_MOVE_IN AS \"whsLocationCode\",\n" +
            "   		'调拨管理' AS \"orderType\",\n" +
            "    		OWM.WHS_MOVE_NO AS \"spmsId\",\n" +
            "    		'提交SAP失败' AS \"spmsStatus\",\n" +
            "    		TO_CHAR(OWM.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
            "    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_MOVE OWM \n" +
            "		WHERE OWM.SPMS_STATUS = '3' \n" +
            "		AND ('0' = #{documentType} OR '10' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWM.WERKS_MOVE_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWM.WHS_LOCATION_CODE_MOVE_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWM.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWM.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		WM.WERKS_MOVE_IN AS \"werks\",\n" +
            "   		WM.WHS_LOCATION_CODE_MOVE_IN AS \"whsLocationCode\",\n" +
            "   		'移仓管理' AS \"orderType\",\n" +
            "    		WM.WHS_MOVE_NO AS \"spmsId\",\n" +
            "    		'待上传SAP' AS \"spmsStatus\",\n" +
            "    		TO_CHAR(WM.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
            "    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
            "    	FROM \n" +
            "    		WHS_MOVE WM \n" +
            "		WHERE WM.SPMS_STATUS = '3' \n" +
            "		AND ('0' = #{documentType} OR '7' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND WM.WERKS_MOVE_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND WM.WHS_LOCATION_CODE_MOVE_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND WM.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND WM.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OWD.WERKS_DUMP_IN AS \"werks\",\n" +
            "   		OWD.WHS_LOCATION_CODE_DUMP_IN AS \"whsLocationCode\",\n" +
            "   		'转储申请' AS \"orderType\",\n" +
            "    		OWD.WHS_DUMP_NO AS \"spmsId\",\n" +
            "    		'提交SAP失败' AS \"spmsStatus\",\n" +
            "    		TO_CHAR(OWD.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
            "    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_DUMP OWD \n" +
            "		WHERE OWD.SPMS_STATUS = '7' \n" +
            "		AND ('0' = #{documentType} OR '8' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWD.WERKS_DUMP_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWD.WHS_LOCATION_CODE_DUMP_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWD.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWD.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OOT.WERKS AS \"werks\",\n" +
            "   		OOT.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "   		'其他出入库' AS \"orderType\",\n" +
            "    		OOT.SPMS_ID AS \"spmsId\",\n" +
            "    		'提交SAP失败' AS \"spmsStatus\",\n" +
            "    		TO_CHAR(OOT.CREATE_TIME, 'yyyy-MM-dd HH:mm:ss') AS \"createTime\",\n" +
            "    		TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_OTHER_TASK OOT \n" +
            "		WHERE OOT.SPMS_STATUS = '5' \n" +
            "		AND ('0' = #{documentType} OR '9' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OOT.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OOT.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OOT.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OOT.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            ") DD \n" +
            "WHERE 1=1 \n" +
            "</script>")
	List<Map<String, Object>> queryAnomaliesRateByPage(Page<Map<String, Object>> pages, 
													@Param("werks") String werks,
													@Param("whsLocationCode") String whsLocationCode, 
													@Param("beginDate") String beginDate, 
													@Param("endDate") String endDate, 
													@Param("documentType") String documentType);
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.\"werks\",DD.\"whsLocationCode\",DD.\"orderType\", COUNT(1) AS \"orderCount\" \n" +
            "FROM (\n" +
    		"       SELECT \n" +
    		"    		OWI.WERKS AS \"werks\",\n" +
			"   		OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"   		'所有单据' AS \"orderType\"\n" +
			"    	FROM \n" +
			"    		OVERSEA_WHS_IN OWI \n" +
            "		WHERE OWI.SPMS_STATUS != '7' \n" +
			"		AND ('0' = #{documentType} OR '1' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWI.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWI.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWI.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWI.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OOBR.WERKS AS \"werks\",\n" +
			"   		OOBR.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"   		'所有单据' AS \"orderType\"\n" +
			"    	FROM \n" +
			"    		OVERSEA_OTHER_BATCH_RECEIPT OOBR \n" +
            "		WHERE OOBR.SPMS_STATUS != '0' \n" +
            "		AND ('0' = #{documentType} OR '2' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OOBR.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OOBR.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OOBR.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OOBR.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OD.WERKS_RECEIVE AS \"werks\",\n" +
            "   		OD.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCode\",\n" +
            "   		'所有单据' AS \"orderType\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_DN OD \n" +
            "		WHERE OD.SPMS_STATUS != '10' \n" +
            " 		<when test='documentType != null and documentType != \"0\" and documentType != \"3\" and documentType != \"4\"'> AND OD.BUSINESS_TYPE = 'XX'</when>" +
            " 		<when test='documentType != null and documentType == \"3\"'> AND OD.BUSINESS_TYPE = '01'</when>" +
            " 		<when test='documentType != null and documentType == \"4\"'> AND OD.BUSINESS_TYPE = '03'</when>" +
            " 		<when test='documentType != null and documentType == \"0\"'> AND OD.BUSINESS_TYPE IN ('01','03')</when>" +
            " 		<when test='werks != null and werks != \"\"'> AND OD.WERKS_RECEIVE = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OD.WHS_LOCATION_CODE_RECEIVE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OD.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OD.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OICA.WERKS AS \"werks\",\n" +
            "   		OICA.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "   		'所有单据' AS \"orderType\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_INV_CHECK_ACTION OICA \n" +
            "		WHERE OICA.SPMS_STATUS != '8' \n" +
            "		AND ('0' = #{documentType} OR '5' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OICA.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OICA.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OICA.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OICA.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OWO.WERKS AS \"werks\",\n" +
            "   		OWO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "   		'所有单据' AS \"orderType\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_OUT OWO \n" +
            "		WHERE OWO.SPMS_STATUS != '0' \n" +
            "		AND ('0' = #{documentType} OR '6' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWO.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWO.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWO.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWO.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OWM.WERKS_MOVE_IN AS \"werks\",\n" +
            "   		OWM.WHS_LOCATION_CODE_MOVE_IN AS \"whsLocationCode\",\n" +
            "   		'所有单据' AS \"orderType\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_MOVE OWM \n" +
            "		WHERE OWM.SPMS_STATUS != '0' \n" +
            "		AND ('0' = #{documentType} OR '10' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWM.WERKS_MOVE_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWM.WHS_LOCATION_CODE_MOVE_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWM.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWM.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		WM.WERKS_MOVE_IN AS \"werks\",\n" +
            "   		WM.WHS_LOCATION_CODE_MOVE_IN AS \"whsLocationCode\",\n" +
            "   		'所有单据' AS \"orderType\"\n" +
            "    	FROM \n" +
            "    		WHS_MOVE WM \n" +
            "		WHERE WM.SPMS_STATUS != '0' \n" +
            "		AND ('0' = #{documentType} OR '7' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND WM.WERKS_MOVE_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND WM.WHS_LOCATION_CODE_MOVE_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND WM.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND WM.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OWD.WERKS_DUMP_IN AS \"werks\",\n" +
            "   		OWD.WHS_LOCATION_CODE_DUMP_IN AS \"whsLocationCode\",\n" +
            "   		'所有单据' AS \"orderType\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_DUMP OWD \n" +
            "		WHERE OWD.SPMS_STATUS != '0' \n" +
            "		AND ('0' = #{documentType} OR '8' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWD.WERKS_DUMP_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWD.WHS_LOCATION_CODE_DUMP_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWD.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWD.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		OOT.WERKS AS \"werks\",\n" +
            "   		OOT.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "   		'所有单据' AS \"orderType\"\n" +
            "    	FROM \n" +
            "    		OVERSEA_OTHER_TASK OOT \n" +
            "		WHERE OOT.SPMS_STATUS != '0' \n" +
            "		AND ('0' = #{documentType} OR '9' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OOT.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OOT.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OOT.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OOT.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            ") DD \n" +
            "WHERE 1=1 \n" +
            "GROUP BY DD.\"werks\", DD.\"whsLocationCode\",DD.\"orderType\" \n" +
            "</script>")
    List<Map<String, Object>> queryAnomaliesRateAllOrderCount(@Param("werks") String werks,
										@Param("whsLocationCode") String whsLocationCode, 
										@Param("beginDate") String beginDate, 
										@Param("endDate") String endDate, 
										@Param("documentType") String documentType);
    @Select("<script>" +
            "SELECT \n" +
    		"	SUM(DD.orderCount) AS  \"orderCount\"\n" +
            "FROM (\n" +
    		"       SELECT \n" +
			"    		COUNT(1) AS orderCount\n" +
			"    	FROM \n" +
			"    		OVERSEA_WHS_IN OWI \n" +
            "		WHERE OWI.SPMS_STATUS = '5' \n" +
			"		AND ('0' = #{documentType} OR '1' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWI.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWI.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWI.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWI.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		COUNT(1) AS orderCount\n" +
			"    	FROM \n" +
			"    		OVERSEA_OTHER_BATCH_RECEIPT OOBR \n" +
            "		WHERE OOBR.SPMS_STATUS = '4' \n" +
            "		AND ('0' = #{documentType} OR '2' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OOBR.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OOBR.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OOBR.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OOBR.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		COUNT(1) AS orderCount\n" +
            "    	FROM \n" +
            "    		OVERSEA_DN OD \n" +
            "		WHERE OD.SPMS_STATUS IN ('5','6') \n" +
            " 		<when test='documentType != null and documentType != \"0\" and documentType != \"3\" and documentType != \"4\"'> AND OD.BUSINESS_TYPE = 'XX'</when>" +
            " 		<when test='documentType != null and documentType == \"3\"'> AND OD.BUSINESS_TYPE = '01'</when>" +
            " 		<when test='documentType != null and documentType == \"4\"'> AND OD.BUSINESS_TYPE = '03'</when>" +
            " 		<when test='documentType != null and documentType == \"0\"'> AND OD.BUSINESS_TYPE IN ('01','03')</when>" +
            " 		<when test='werks != null and werks != \"\"'> AND OD.WERKS_RECEIVE = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OD.WHS_LOCATION_CODE_RECEIVE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OD.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OD.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		COUNT(1) AS orderCount\n" +
            "    	FROM \n" +
            "    		OVERSEA_INV_CHECK_ACTION OICA \n" +
            "		WHERE OICA.SPMS_STATUS IN ('3','6') \n" +
            "		AND ('0' = #{documentType} OR '5' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OICA.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OICA.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OICA.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OICA.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		COUNT(1) AS orderCount\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_OUT OWO \n" +
            "		WHERE OWO.SPMS_STATUS IN ('2','4') \n" +
            "		AND ('0' = #{documentType} OR '6' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWO.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWO.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWO.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWO.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		COUNT(1) AS orderCount\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_MOVE OWM \n" +
            "		WHERE OWM.SPMS_STATUS = '3' \n" +
            "		AND ('0' = #{documentType} OR '10' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWM.WERKS_MOVE_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWM.WHS_LOCATION_CODE_MOVE_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWM.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWM.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		COUNT(1) AS orderCount\n" +
            "    	FROM \n" +
            "    		WHS_MOVE WM \n" +
            "		WHERE WM.SPMS_STATUS = '3' \n" +
            "		AND ('0' = #{documentType} OR '7' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND WM.WERKS_MOVE_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND WM.WHS_LOCATION_CODE_MOVE_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND WM.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND WM.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		COUNT(1) AS orderCount\n" +
            "    	FROM \n" +
            "    		OVERSEA_WHS_DUMP OWD \n" +
            "		WHERE OWD.SPMS_STATUS = '7' \n" +
            "		AND ('0' = #{documentType} OR '8' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OWD.WERKS_DUMP_IN = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OWD.WHS_LOCATION_CODE_DUMP_IN = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OWD.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OWD.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "		UNION ALL \n" +
            "       SELECT \n" +
            "    		COUNT(1) AS orderCount\n" +
            "    	FROM \n" +
            "    		OVERSEA_OTHER_TASK OOT \n" +
            "		WHERE OOT.SPMS_STATUS = '5' \n" +
            "		AND ('0' = #{documentType} OR '9' = #{documentType} ) \n" +
            " 		<when test='werks != null and werks != \"\"'> AND OOT.WERKS = #{werks}</when>" +
            " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND OOT.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            " 		<when test='beginDate!=null'><![CDATA[ AND OOT.CREATE_TIME >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 		<when test='endDate!=null'> <![CDATA[ AND OOT.CREATE_TIME <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            ") DD \n" +
            "WHERE 1=1 \n" +
            "</script>")
	int queryAnomaliesRateFailOrderCount(@Param("werks") String werks,
										@Param("whsLocationCode") String whsLocationCode, 
										@Param("beginDate") String beginDate, 
										@Param("endDate") String endDate, 
										@Param("documentType") String documentType);
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.*,DD.dkDate AS \"dkDate\" \n" +
            "FROM (\n" +
    		"       SELECT \n" +
    		"    		ZPZ.FHWERKS AS \"fhWerks\",\n" +
			"   		ZPZ.SHWERKS AS \"shWerks\",\n" +
			"   		ZPZ.BEDAT AS \"orderDate\",\n" +
			"   		ZPZ.EBELN AS \"orderNumber\",\n" +
			"   		ZPZ.EBELP AS \"orderItemNumber\",\n" +
			"   		(CASE WHEN ZPZ.BOLNR IS NOT NULL THEN TO_CHAR(TO_DATE(ZPZ.DGDAT, 'yyyy-MM-dd')+21, 'yyyy-MM-dd') WHEN ZPZ.BOLNR IS NULL AND ZPZ.FHWERKS IN ('CN01','CN11','CN61','HK01','HK11','HK21','AE01','MU0') THEN TO_CHAR(TO_DATE(ZPZ.BEDAT, 'yyyy-MM-dd')+3, 'yyyy-MM-dd') WHEN ZPZ.FHWERKS IS NULL THEN TO_CHAR(TO_DATE(ZPZ.BEDAT, 'yyyy-MM-dd')+7, 'yyyy-MM-dd') WHEN ZPZ.FHWERKS NOT IN ('CN01','CN11','CN61','HK01','HK11','HK21','AE01','MU0') THEN TO_CHAR(TO_DATE(ZPZ.BEDAT, 'yyyy-MM-dd')+14, 'yyyy-MM-dd') ELSE NULL END) AS dkDate,\n" +
			"   		ZPZ.DGDAT AS \"dgdat\",\n" +
			"   		ZPZ.BOLNR AS \"packageList\",\n" +
			"   		ZPZ.CABNO AS \"cabinetNumber\",\n" +
			"   		(CASE WHEN ZPZ.BOLNR IS NOT NULL THEN '海运收货' WHEN ZPZ.BOLNR IS NULL AND ZPZ.FHWERKS IN ('CN01','CN11','CN61','HK01','HK11','HK21','AE01','MU0') THEN '空运人带' WHEN ZPZ.FHWERKS IS NULL THEN '海外本地采购' WHEN ZPZ.FHWERKS NOT IN ('CN01','CN11','CN61','HK01','HK11','HK21','AE01','MU0') THEN '海外转储' ELSE NULL END) AS \"orderType\",\n" +
			"   		ZPZ.VBELN AS \"vbeln\",\n" +
			"   		ZPZ.NUMC AS \"numc\",\n" +
			"   		ZPZ.PACKAGE_CODE AS \"packageCode\",\n" +
			"   		ZPZ.MATNR AS \"matnr\",\n" +
			"   		ZPZ.MAKTX AS \"maktx\",\n" +
			"   		ZPZ.WAMNG AS \"wamng\",\n" +
			"   		ZPZ.WEMNG AS \"wemng\",\n" +
			"   		ZPZ.MEINS AS \"meins\",\n" +
			"   		ZPZ.ZWAMNG_XS AS \"zwamngxs\",\n" +
			"   		ZPZ.ZWEMNG_XS AS \"zwemngxs\",\n" +
			"   		ZPZ.ZVRKME_XS AS \"zvrkmexs\",\n" +
			"			TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
			"    	FROM \n" +
			"    		Z_SPMS_ZAITU ZPZ \n" +
            "		WHERE 1=1 \n" +
            " 		<when test='werks != null and werks != \"\"'> AND ZPZ.SHWERKS = #{werks}</when>" +
           // " 		<when test='whsLocationCode != null and whsLocationCode != \"\"'> AND ZPZ.WHS_LOCATION_CODE = #{whsLocationCode}</when>" +
            //" 		<when test='beginDate!=null'><![CDATA[ AND TO_DATE(ZPZ.DGDAT, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            //" 		<when test='endDate!=null'> <![CDATA[ AND TO_DATE(ZPZ.DGDAT, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            ") DD \n" +
            "WHERE 1=1 \n" +
            " 	<when test='beginDate!=null'><![CDATA[ AND TO_DATE(DD.dkDate, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 	<when test='endDate!=null'> <![CDATA[ AND TO_DATE(DD.dkDate, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "</script>")
	List<Map<String, Object>> queryOnwayRateByPage(Page<Map<String, Object>> pages,
												@Param("werks") String werks,
												@Param("whsLocationCode") String whsLocationCode, 
												@Param("beginDate") String beginDate, 
												@Param("endDate") String endDate, 
												@Param("documentType") String documentType);
    
    @Select("<script>" +
            "SELECT \n" +
    		"	DD.*,DD.dkDate AS \"dkDate\" \n" +
            "FROM (\n" +
    		"       SELECT \n" +
    		"    		ZPZ.FHWERKS AS \"fhWerks\",\n" +
			"   		ZPZ.SHWERKS AS \"shWerks\",\n" +
			"   		ZPZ.BEDAT AS \"orderDate\",\n" +
			"   		ZPZ.EBELN AS \"orderNumber\",\n" +
			"   		ZPZ.EBELP AS \"orderItemNumber\",\n" +
			"   		(CASE WHEN ZPZ.BOLNR IS NOT NULL THEN TO_CHAR(TO_DATE(ZPZ.DGDAT, 'yyyy-MM-dd')+21, 'yyyy-MM-dd') WHEN ZPZ.BOLNR IS NULL AND ZPZ.FHWERKS IN ('CN01','CN11','CN61','HK01','HK11','HK21','AE01','MU0') THEN TO_CHAR(TO_DATE(ZPZ.BEDAT, 'yyyy-MM-dd')+3, 'yyyy-MM-dd') WHEN ZPZ.FHWERKS IS NULL THEN TO_CHAR(TO_DATE(ZPZ.BEDAT, 'yyyy-MM-dd')+7, 'yyyy-MM-dd') WHEN ZPZ.FHWERKS NOT IN ('CN01','CN11','CN61','HK01','HK11','HK21','AE01','MU0') THEN TO_CHAR(TO_DATE(ZPZ.BEDAT, 'yyyy-MM-dd')+14, 'yyyy-MM-dd') ELSE NULL END) AS dkDate,\n" +
			"   		ZPZ.DGDAT AS \"dgdat\",\n" +
			"   		ZPZ.BOLNR AS \"packageList\",\n" +
			"   		ZPZ.CABNO AS \"cabinetNumber\",\n" +
			"   		(CASE WHEN ZPZ.BOLNR IS NOT NULL THEN '海运收货' WHEN ZPZ.BOLNR IS NULL AND ZPZ.FHWERKS IN ('CN01','CN11','CN61','HK01','HK11','HK21','AE01','MU0') THEN '空运人带' WHEN ZPZ.FHWERKS IS NULL THEN '海外本地采购' WHEN ZPZ.FHWERKS NOT IN ('CN01','CN11','CN61','HK01','HK11','HK21','AE01','MU0') THEN '海外转储' ELSE NULL END) AS \"orderType\",\n" +
			"   		ZPZ.VBELN AS \"vbeln\",\n" +
			"   		ZPZ.NUMC AS \"numc\",\n" +
			"   		ZPZ.PACKAGE_CODE AS \"packageCode\",\n" +
			"   		ZPZ.MATNR AS \"matnr\",\n" +
			"   		ZPZ.MAKTX AS \"maktx\",\n" +
			"   		ZPZ.WAMNG AS \"wamng\",\n" +
			"   		ZPZ.WEMNG AS \"wemng\",\n" +
			"   		ZPZ.MEINS AS \"meins\",\n" +
			"   		ZPZ.ZWAMNG_XS AS \"zwamngxs\",\n" +
			"   		ZPZ.ZWEMNG_XS AS \"zwemngxs\",\n" +
			"   		ZPZ.ZVRKME_XS AS \"zvrkmexs\",\n" +
			"			TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss') AS \"nowDate\"\n" +
			"    	FROM \n" +
			"    		Z_SPMS_ZAITU ZPZ \n" +
            "		WHERE 1=1 \n" +
            " 		<when test='werks != null and werks != \"\"'> AND ZPZ.SHWERKS = #{werks}</when>" +
            ") DD \n" +
            "WHERE 1=1 \n" +
            " 	<when test='beginDate!=null'><![CDATA[ AND TO_DATE(DD.dkDate, 'yyyy-MM-dd') >= TO_DATE(#{beginDate}, 'yyyy-MM-dd')]]></when>" +
            " 	<when test='endDate!=null'> <![CDATA[ AND TO_DATE(DD.dkDate, 'yyyy-MM-dd') <= TO_DATE(#{endDate}, 'yyyy-MM-dd') ]]></when>" +
            "</script>")
	List<Map<String, Object>> queryOnwayRateByList(@Param("werks") String werks,
												@Param("whsLocationCode") String whsLocationCode, 
												@Param("beginDate") String beginDate, 
												@Param("endDate") String endDate, 
												@Param("documentType") String documentType);

    /** getOrderKeyDates 对应完整SQL语句如下：
     SELECT ROWNUM AS "rowId", RESULT.* FROM (
     SELECT OW.WERKS AS "werks",
     W.WERKS_NAME_ZH AS "werksNameZh",
     OWS.WORKSHOP_CODE AS "workshopCode",
     WS.WORKSHOP_NAME_ZH AS "workshopNameZh",
     OWS.WORK_CODE AS "orderWorkshopUserid",
     OWS.LAST_NAME AS "orderWorkshopUsername",
     OW.WORK_CODE AS "orderWerksUserid",
     OW.LAST_NAME AS "orderWerksUsername",
     OW.SPMS_ID AS "orderWerksId",
     OWD.ROW_ID_WERKS AS "orderWerksRowId",
     OW.OA_PROCESS_ID AS "oaProcessId",
     OW.APPLICATION_DATE AS "orderWerksApplicationDate",
     OW.OA_APPROVAL_DATE AS "oaApprovalDate",
     OW.URGENCY AS "urgency",
     OW.FIELD_FOR_OA AS "orderWerksFieldForOa",
     OW.SHIPPING_TYPE AS "orderWerksShippingType",
     OW.SHIPPING_FANGSHI AS "orderWerksShippingFangshi",
     OW.JUSTIFICATION AS "orderWerksJustification",
     OWS.SUBJECT AS "orderWorkshopSubject",
     OWS.SPMS_WS_ID AS "orderWorkshopId",
     OWSD.ROW_ID AS "orderWorkshopRowId",
     OWS.APPLICATION_DATE AS "orderWorkshopApplicationDate",
     OWSD.MATERIAL_NO AS "materialNo",
     SAP.MATERIAL_ZH_DESC AS "materialZhDesc",
     SAP.MATERIAL_EN_DESC AS "materialEnDesc",
     OWSD.REQUEST_QTY AS "requestQty",
     OWSD.APPROVAL_QTY AS "approvalQty",
     SAP.BASIC_UNIT AS "basicUnit",
     NVL(SI.SAFETY_QTY_BASIC_UNIT, 0) AS "safetyQtyBasicUnit",
     NVL(WI.WERKS_INVENTORY, 0) AS "werksInventory",
     NVL(WAY.WAY_QUANTITY, 0) AS "wayQuantity",
     SBND2.BOX_NOTE AS "boxNote",
     SBND2.ITEM AS "boxNoteItem",
     SBND2.DOMESTIC_QTY_RECEIVED AS "domesticQtyReceived",
     BOX_LOG.OPERATION_TYPE AS "operationType",
     (CASE WHEN SBND2.BOX_NOTE IS NULL THEN '1'
     WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '3'
     WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '4'
     WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '5'
     WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '6'
     ELSE '2' END) AS "boxStatus",
     (CASE WHEN SBND2.BOX_NOTE IS NULL THEN '箱码待生成'
     WHEN BOX_LOG.OPERATION_TYPE IN ('2', '7', '12', '18', '19') THEN '自管仓'
     WHEN BOX_LOG.OPERATION_TYPE = '8' THEN '盘亏'
     WHEN BOX_LOG.OPERATION_TYPE IN ('3', '16', '20', '22') THEN '自管仓已发出'
     WHEN BOX_LOG.OPERATION_TYPE = '21' THEN '海外仓已到货'
     ELSE '自管仓待到货' END) AS "boxStatusDesc",
     SBND2.DELIVERY_NOTE_SAP AS "deliveryNote",
     (CASE WHEN SDN.DOCUMENT_DATE IS NOT NULL
     THEN TO_CHAR(TO_DATE(SDN.DOCUMENT_DATE, 'yyyyMMdd'), 'yyyy-MM-dd')
     END) AS "domesticArrivalDate",
     SPLB.PACKAGE_LIST AS "packageList",
     SPLB.WHS_OUT_UUID AS "whsOutUuid",
     (CASE WHEN CHN_OUT.CREATE_TIME IS NOT NULL
     THEN TO_CHAR(CHN_OUT.CREATE_TIME, 'yyyy-MM-dd')
     END) AS "domesticDeliveryDate",
     (CASE WHEN OVERSEA_BOX.CREATE_TIME IS NOT NULL
     THEN TO_CHAR(OVERSEA_BOX.CREATE_TIME, 'yyyy-MM-dd')
     END) AS "overseaArrivalDate",
     (CASE WHEN OVERSEA_BOX.CREATE_TIME IS NOT NULL
     THEN TO_DATE(TO_CHAR(OVERSEA_BOX.CREATE_TIME, 'yyyy-MM-dd'), 'yyyy-MM-dd') -
     TO_DATE(OWS.APPLICATION_DATE, 'yyyy-MM-dd')
     END) AS "daysSpent"
     FROM ORDER_WERKS OW
     LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID
     LEFT JOIN ORDER_WORKSHOP_DTL OWSD ON OWD.SPMS_WS_ID = OWSD.SPMS_WS_ID AND OWD.ROW_ID_WS = OWSD.ROW_ID
     LEFT JOIN ORDER_WORKSHOP OWS ON OWSD.UUID = OWS.UUID
     LEFT JOIN WERKS W ON OW.WERKS = W.WERKS
     LEFT JOIN WORKSHOP WS ON OW.WERKS = WS.WERKS AND OWS.WORKSHOP_CODE = WS.WORKSHOP_CODE
     LEFT JOIN MATERIAL_SAP SAP ON OWSD.MATERIAL_NO = SAP.MATERIAL_NO
     LEFT JOIN SAFETY_INVENTORY SI ON OW.WERKS = SI.WERKS AND OWSD.MATERIAL_NO = SI.MATERIAL_NO
     LEFT JOIN (
     SELECT WS.WERKS, WSI.MATERIAL_NO, SUM(WSI.QUANTITY) AS "WERKS_INVENTORY"
     FROM WHS_STORAGE_INVENTORY WSI
     LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID
     GROUP BY WS.WERKS, WSI.MATERIAL_NO
     ) WI ON OW.WERKS = WI.WERKS AND OWSD.MATERIAL_NO = WI.MATERIAL_NO
     LEFT JOIN (
     SELECT T.WERKS, T.MATERIAL_NO, SUM(T.DOMESTIC_QTY_RECEIVED) AS "WAY_QUANTITY"
     FROM (
     SELECT SUBSTR(SBND.DEMAND_NO, 0, 4) AS "WERKS", SBND.MATERIAL_NO, SBND.DOMESTIC_QTY_RECEIVED
     FROM (
     SELECT MAX(LOG_AUTO_ID) AS "ID", L.BOX_NOTE
     FROM WHS_OPERATE_LOG L
     WHERE L.BOX_NOTE IS NOT NULL AND L.WERKS = 'CN01'
     AND L.OPERATION_TYPE IN ('3', '16', '22') AND L.COMMENTS != '自管仓备件入库撤销下架'
     GROUP BY L.BOX_NOTE
     ) R LEFT JOIN SAP_BOX_NOTE_DTL SBND ON R.BOX_NOTE = SBND.BOX_NOTE
     ) T GROUP BY T.WERKS, T.MATERIAL_NO
     ) WAY ON OW.WERKS = WAY.WERKS AND OWSD.MATERIAL_NO = WAY.MATERIAL_NO
     LEFT JOIN SAP_BOX_NOTE_DTL SBND2 ON OW.SPMS_ID = SBND2.DEMAND_NO AND OWD.ROW_ID_WERKS = SBND2.DEMAND_NO_ITEM
     LEFT JOIN (
     SELECT BL.ID, L.WERKS, L.WHS_LOCATION_CODE, L.BOX_NOTE, L.DOCUMENT_TYPE, L.OPERATION_TYPE, L.COMMENTS, L.REMARK, L.CREATE_TIME
     FROM (
     SELECT MAX(LOG_AUTO_ID) AS "ID"
     FROM WHS_OPERATE_LOG WOL
     WHERE WOL.DOCUMENT_TYPE IN ('2', '3') AND WOL.BOX_NOTE IS NOT NULL GROUP BY BOX_NOTE
     )BL LEFT JOIN WHS_OPERATE_LOG L ON BL.ID = L.LOG_AUTO_ID ORDER BY TO_NUMBER(L.OPERATION_TYPE) DESC
     ) BOX_LOG ON BOX_LOG.BOX_NOTE = SBND2.BOX_NOTE
     LEFT JOIN (
     SELECT DISTINCT SPB.BOX_NOTE, SPB.PACKAGE_LIST, SPB.WHS_OUT_UUID FROM SAP_PACKAGE_BOX SPB WHERE ACTUAL_OPERATE_QTY > 0
     ) SPLB ON SBND2.BOX_NOTE = SPLB.BOX_NOTE
     LEFT JOIN SAP_DELIVERY_NOTE SDN ON SBND2.DELIVERY_NOTE_SAP = SDN.DELIVERY_NOTE_SAP
     LEFT JOIN WHS_OUT WO ON SPLB.WHS_OUT_UUID = WO.UUID
     LEFT JOIN (
     SELECT T.ID, T.BOX_NOTE, L.CREATE_TIME FROM (
     SELECT MAX(WOL.LOG_AUTO_ID) AS "ID", WOL.BOX_NOTE
     FROM WHS_OPERATE_LOG WOL
     WHERE WOL.BOX_NOTE IS NOT NULL AND WOL.WERKS = 'CN01'
     AND WOL.OPERATION_TYPE IN ('3', '16', '22') AND WOL.COMMENTS != '自管仓备件入库撤销下架'
     GROUP BY WOL.BOX_NOTE
     ) T LEFT JOIN WHS_OPERATE_LOG L ON T.ID = L.LOG_AUTO_ID
     ) CHN_OUT ON SBND2.BOX_NOTE = CHN_OUT.BOX_NOTE
     LEFT JOIN (
     SELECT MAX(OL.LOG_AUTO_ID) AS "ID", OL.BOX_NOTE, OL.CREATE_TIME
     FROM WHS_OPERATE_LOG OL
     WHERE OL.BOX_NOTE IS NOT NULL AND OL.OPERATION_TYPE = '21'
     GROUP BY OL.BOX_NOTE, OL.CREATE_TIME
     ) OVERSEA_BOX ON SBND2.BOX_NOTE = OVERSEA_BOX.BOX_NOTE
     WHERE OW.WERKS = 'TF01'
     ORDER BY OW.SPMS_ID, OWD.ROW_ID_WERKS, SBND2.BOX_NOTE NULLS LAST, SBND2.ITEM
     ) RESULT
     */
}