package com.sunda.spmsoversea.mapper;

import com.sunda.spmsoversea.entity.OverseaWhsDumpDtl;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * <p>
 * 海外转储任务明细表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-11-02
 */
@Mapper
public interface OverseaWhsDumpDtlMapper extends BaseMapper<OverseaWhsDumpDtl> {

    @Select("SELECT OWDD.AUTO_ID_DUMP_DTL AS \"autoIdDumpDtl\",\n" +
            "       OWDD.WHS_DUMP_UUID AS \"whsDumpUuid\",\n" +
            "       OWDD.ITEM AS \"item\",\n" +
            "       OWDD.MATERIAL_NO AS \"materialNo\",\n" +
            "       OWDD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       OWDD.UNIT_SALES AS \"unitSales\",\n" +
            "       OWDD.APPLY_QTY_BASIC_UNIT AS \"applyQtyBasicUnit\",\n" +
            "       OWDD.APPLY_QTY_UNIT_SALES AS \"applyQtyUnitSales\",\n" +
            "       OWDD.APPLY_REMARK AS \"applyRemark\",\n" +
            "       OWDD.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\",\n" +
            "       OWDD.ACTUAL_QTY_UNIT_SALES AS \"actualQtyUnitSales\",\n" +
            "       OWDD.ACTUAL_STORAGE_INFO AS \"actualStorageInfo\",\n" +
            "       OWDD.ACTUAL_REMARK AS \"actualRemark\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\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" +
            "FROM OVERSEA_WHS_DUMP_DTL OWDD\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON OWDD.MATERIAL_NO = SAP.MATERIAL_NO \n" +
            "WHERE OWDD.WHS_DUMP_UUID = #{whsDumpUuid} \n" +
            "ORDER BY OWDD.ITEM ASC ")
    List<Map<String, Object>> getWhsDumpDtlList(@Param("whsDumpUuid") String whsDumpUuid);

    @Select("SELECT \n" +
            "OWD.WHS_DUMP_NO AS \"whsDumpNo\", \n" +
            "OWD.SPMS_STATUS AS \"spmsStatus\", \n" +
            "OWD.WERKS_DUMP_OUT AS \"werksDumpOut\", \n" +
            "OWD.WHS_LOCATION_CODE_DUMP_OUT AS \"whsLocationCodeDumpOut\", \n" +
            "OWD.WERKS_DUMP_IN AS \"werksDumpIn\", \n" +
            "OWD.WHS_LOCATION_CODE_DUMP_IN AS \"whsLocationCodeDumpIn\", \n" +
            "OWD.CREATE_DATE AS \"createDate\", \n" +
            "OWD.DUMP_OUT_DATE AS \"dumpOutDate\", \n" +
            "OWD.OA_BIANHAO AS \"oaBianhao\", \n" +
            "OWD.OA_APPROVAL_DATE AS \"oaApprovalDate\", \n" +
            "OWD.CREATE_USERID AS \"createUserid\", \n" +
            "OWDD.AUTO_ID_DUMP_DTL AS \"autoIdDumpDtl\", \n" +
            "OWDD.WHS_DUMP_UUID AS \"whsDumpUuid\", \n" +
            "OWDD.ITEM AS \"item\", \n" +
            "OWDD.MATERIAL_NO AS \"materialNo\", \n" +
            "OWDD.BASIC_UNIT AS \"basicUnit\", \n" +
            "OWDD.UNIT_SALES AS \"unitSales\", \n" +
            "OWDD.APPLY_QTY_BASIC_UNIT AS \"applyQtyBasicUnit\", \n" +
            "OWDD.APPLY_QTY_UNIT_SALES AS \"applyQtyUnitSales\", \n" +
            "OWDD.APPLY_REMARK AS \"applyRemark\", \n" +
            "OWDD.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\", \n" +
            "OWDD.ACTUAL_QTY_UNIT_SALES AS \"actualQtyUnitSales\", \n" +
            "OWDD.ACTUAL_STORAGE_INFO AS \"actualStorageInfo\", \n" +
            "OWDD.ACTUAL_REMARK AS \"actualRemark\", \n" +
            "SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\", \n" +
            "SAP.MATERIAL_EN_DESC AS \"materialEnDesc\", \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" +
            "FROM \n" +
            "OVERSEA_WHS_DUMP_DTL OWDD \n" +
            "LEFT JOIN OVERSEA_WHS_DUMP OWD ON OWDD.WHS_DUMP_UUID = OWD.WHS_DUMP_UUID \n" +
            "LEFT JOIN MATERIAL_SAP SAP ON OWDD.MATERIAL_NO = SAP.MATERIAL_NO \n" +
            "WHERE \n" +
            "OWDD.WHS_DUMP_UUID =  #{whsDumpUuid} \n" +
            "ORDER BY \n" +
            "OWDD.ITEM ASC ")
    List<Map<String, Object>> getWhsDumpDtlListExcel(@Param("whsDumpUuid") String whsDumpUuid);


}