package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.SapPackageList;

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

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

/**
 * <p>
 * SAP装箱单表抬头 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-04-22
 */
@Mapper
public interface SapPackageListMapper extends BaseMapper<SapPackageList> {

    @Select("<script>SELECT\n" +
            "       ROWNUM AS \"rowId\",\n" +
            "       PL.PACKAGE_LIST AS \"packageList\",\n" +
            "       PL.CUSTOMER AS \"customer\",\n" +
            "       PL.SPARE_PARTS AS \"spareParts\",\n" +
            "       PL.PLANNED_CABINETS_QTY AS \"plannedCabinetsQty\",\n" +
            "       PL.FROM_PORT AS \"fromPort\",\n" +
            "       PL.FROM_PORT_NAME AS \"fromPortName\",\n" +
            "       PL.TO_PORT AS \"toPort\",\n" +
            "       PL.TO_PORT_NAME AS \"toPortName\",\n" +
            "       PL.SHIP_COMPANY AS \"shipCompany\",\n" +
            "       PL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       PL.PLANNER AS \"planner\",\n" +
            "       PL.LOADER AS \"loader\",\n" +
            "       PL.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       PL.SPMS_STATUS AS \"spmsStatus\"\n" +
            "FROM SAP_PACKAGE_LIST PL WHERE PL.PACKAGE_LIST IN (\n" +
            "    SELECT DISTINCT PLD.PACKAGE_LIST\n" +
            "    FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    WHERE PLD.LOADING_WERKS\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WERKS FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            "      AND PLD.LOADING_WHS_LOCATION_CODE\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WHS_LOCATION_CODE FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            ") \n" +
            " <when test='packageList!=null'> AND PL.PACKAGE_LIST = #{packageList}</when> \n" +
            " <when test='spmsStatus!=null'> AND PL.SPMS_STATUS = #{spmsStatus}</when> \n" +
            "ORDER BY PL.SPMS_STATUS ASC </script>\n")
    List<Map<String, Object>> getSapPackageList(@Param("userId") String userId, @Param("packageList") String packageList, @Param("spmsStatus") String spmsStatus);

    @Select("<script>SELECT\n" +
            "       ROWNUM AS \"rowId\",\n" +
            "       PL.PACKAGE_LIST AS \"packageList\",\n" +
            "       PL.CUSTOMER AS \"customer\",\n" +
            "       PL.SPARE_PARTS AS \"spareParts\",\n" +
            "       PL.PLANNED_CABINETS_QTY AS \"plannedCabinetsQty\",\n" +
            "       PL.FROM_PORT AS \"fromPort\",\n" +
            "       PL.FROM_PORT_NAME AS \"fromPortName\",\n" +
            "       PL.TO_PORT AS \"toPort\",\n" +
            "       PL.TO_PORT_NAME AS \"toPortName\",\n" +
            "       PL.SHIP_COMPANY AS \"shipCompany\",\n" +
            "       PL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       PL.PLANNER AS \"planner\",\n" +
            "       PL.LOADER AS \"loader\",\n" +
            "       PL.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       PL.PLANNER_NAME AS \"plannerName\",\n" +
            "       PL.PLAN_LOADING_DATE AS \"planLoadingDate\",\n" +
            "       PL.SPMS_STATUS AS \"spmsStatus\"\n" +
            "FROM SAP_PACKAGE_LIST PL WHERE PL.PACKAGE_LIST IN (\n" +
            "    SELECT DISTINCT PLD.PACKAGE_LIST\n" +
            "    FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    WHERE PLD.LOADING_WERKS\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WERKS FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            "      AND PLD.LOADING_WHS_LOCATION_CODE\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WHS_LOCATION_CODE FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            " 		<when test='werks!=null'> AND PLD.LOADING_WERKS = #{werks}</when> \n" +
            " 		<when test='whsLocationCode!=null'> AND PLD.LOADING_WHS_LOCATION_CODE = #{whsLocationCode}</when> \n" +
            ") \n" +
            " <when test='packageList!=null'> AND PL.PACKAGE_LIST = #{packageList}</when> \n" +
            " <when test='spmsStatus!=null'> AND PL.SPMS_STATUS = #{spmsStatus}</when> \n" +
            " <when test='planLoadingDateStart != null'> <![CDATA[ AND PL.PLAN_LOADING_DATE >= #{planLoadingDateStart} ]]> </when> \n" +
            " <when test='planLoadingDateEnd != null'> <![CDATA[ AND PL.PLAN_LOADING_DATE <= #{planLoadingDateEnd} ]]> </when> \n" +
            "ORDER BY PL.SPMS_STATUS ASC, PL.PACKAGE_LIST DESC </script>\n")
    List<Map<String, Object>> getSapPackageListPage(Page<Map<String, Object>> pages,@Param("userId")  String userId,@Param("packageList")  String packageList,@Param("spmsStatus")  String spmsStatus,
    		@Param("planLoadingDateStart") String planLoadingDateStart,@Param("planLoadingDateEnd") String planLoadingDateEnd,@Param("werks") String werks,@Param("whsLocationCode") String whsLocationCode);

    /***
     * 装箱单直装卸货入库时，查询获取物料的基本信息，单位换算关系等；
     * @param materialNo
     * @return
     */
    @Select("SELECT SAP.MATERIAL_NO AS \"materialNo\",\n" +
            "       (CASE WHEN SAP.NUMERATOR IS NULL THEN 1 ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1 ELSE SAP.DENOMINATOR END) AS \"denominator\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM MATERIAL_SAP SAP\n" +
            "WHERE SAP.MATERIAL_NO = #{materialNo} ")
    Map<String, Object> getNumeratorDenominator(@Param("materialNo") String materialNo);
    
    @Select("SELECT WO.* \n" +
            "FROM WHS_OUT WO\n" +
            "WHERE WO.PACKAGE_LIST = #{packageList} ")
	List<Map<String, Object>> selectWhsOutList(@Param("packageList") String packageList);
    
    @Delete("DELETE FROM WHS_OUT WHERE PACKAGE_LIST = #{packageList} ")
	int deleteWhsOutListByPackageList(@Param("packageList") String packageList);
    
    @Select("<script>SELECT\n" +
            "       ROWNUM AS \"rowId\",\n" +
            "       PL.PACKAGE_LIST AS \"packageList\",\n" +
            "       PL.CUSTOMER AS \"customer\",\n" +
            "       PL.SPARE_PARTS AS \"spareParts\",\n" +
            "       PL.PLANNED_CABINETS_QTY AS \"plannedCabinetsQty\",\n" +
            "       PL.FROM_PORT AS \"fromPort\",\n" +
            "       PL.FROM_PORT_NAME AS \"fromPortName\",\n" +
            "       PL.TO_PORT AS \"toPort\",\n" +
            "       PL.TO_PORT_NAME AS \"toPortName\",\n" +
            "       PL.SHIP_COMPANY AS \"shipCompany\",\n" +
            "       PL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       PL.PLANNER AS \"planner\",\n" +
            "       PL.LOADER AS \"loader\",\n" +
            "       PL.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       PL.PLANNER_NAME AS \"plannerName\",\n" +
            "       PL.PLAN_LOADING_DATE AS \"planLoadingDate\",\n" +
            "       PL.SPMS_STATUS AS \"spmsStatus\"\n" +
            "FROM SAP_PACKAGE_LIST PL WHERE PL.PACKAGE_LIST IN (\n" +
            "    SELECT DISTINCT PLD.PACKAGE_LIST\n" +
            "    FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    WHERE PACKAGE_CODE IS NOT NULL AND PLD.LOADING_WERKS\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WERKS FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            "      AND PLD.LOADING_WHS_LOCATION_CODE\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WHS_LOCATION_CODE FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            " 		<when test='werks!=null'> AND PLD.LOADING_WERKS = #{werks}</when> \n" +
            " 		<when test='whsLocationCode!=null'> AND PLD.LOADING_WHS_LOCATION_CODE = #{whsLocationCode}</when> \n" +
            ") \n" +
            " <when test='packageList!=null'> AND PL.PACKAGE_LIST = #{packageList}</when> \n" +
            " <when test='spmsStatus!=null'> AND PL.SPMS_STATUS = #{spmsStatus}</when> \n" +
            " <when test='planLoadingDateStart != null'> <![CDATA[ AND PL.PLAN_LOADING_DATE >= #{planLoadingDateStart} ]]> </when> \n" +
            " <when test='planLoadingDateEnd != null'> <![CDATA[ AND PL.PLAN_LOADING_DATE <= #{planLoadingDateEnd} ]]> </when> \n" +
            "ORDER BY PL.SPMS_STATUS ASC, PL.PACKAGE_LIST DESC </script>\n")
    List<Map<String, Object>> getSapPackageListPageByPda(Page<Map<String, Object>> pages,@Param("userId")  String userId,@Param("packageList")  String packageList,@Param("spmsStatus")  String spmsStatus,
    		@Param("planLoadingDateStart") String planLoadingDateStart,@Param("planLoadingDateEnd") String planLoadingDateEnd,@Param("werks") String werks,@Param("whsLocationCode") String whsLocationCode);
}