package com.sunda.spmsoversea.mapper;

import com.sunda.spmsoversea.entity.OverseaInvCheckDtl;
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 org.springframework.stereotype.Component;

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

/**
 * <p>
 * 盘点任务表明细表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-05-10
 */
@Mapper
@Component
public interface OverseaInvCheckDtlMapper extends BaseMapper<OverseaInvCheckDtl> {

    @Select("SELECT\n" +
            "  ROWNUM AS \"rowId\",\n" +
            "  INVCHKD.UUID AS \"uuid\",\n" +
            "  INVCHKD.TASK_ROW_ID AS \"taskRowId\",\n" +
            "  INVCHKD.MATERIAL_NO AS \"materialNo\",\n" +
            "  MA.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  MA.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  (CASE WHEN MA.UNIT_SALES IS NULL THEN MA.BASIC_UNIT ELSE MA.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN MA.NUMERATOR IS NULL THEN 1 ELSE MA.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN MA.DENOMINATOR IS NULL THEN 1 ELSE MA.DENOMINATOR END) AS \"denominator\"," +
            "  INVCHKD.INVENTORY_QUANTITY AS \"inventoryQuantity\",\n" +
            "  INVCHKD.CHECK_QUANTITY AS \"checkQuantity\",\n" +
            "  INVCHKD.DISCREPANCY AS \"discrepancy\",\n" +
            "  INVCHKD.BASIC_UNIT AS \"basicUnit\",\n" +
            "  INVCHKD.STORAGE_UUID AS \"storageUuid\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  INVCHKD.REMARKS AS \"remarks\"\n" +
            "FROM OVERSEA_INV_CHECK_DTL INVCHKD\n" +
            "       LEFT JOIN MATERIAL_SAP MA ON INVCHKD.MATERIAL_NO=MA.MATERIAL_NO\n" +
            "       LEFT JOIN WHS_STORAGE WHS ON WHS.UUID=INVCHKD.STORAGE_UUID \n" +
            "WHERE INVCHKD.UUID= #{uuid} ")
    List<Map<String, Object>> selectInvDtl(@Param("uuid") String uuid);

    @Select("SELECT \n" +
            "       NVL(MAX(TO_NUMBER(TASK_ROW_ID)),0) AS \"taskRowId\"  \n" +
            "FROM OVERSEA_INV_CHECK_DTL \n" +
            "WHERE UUID= #{uuid}")
    int selectMaxTaskRowId(@Param("uuid") String uuid);

}