package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmswms.entity.InventoryCheckActionDtl;
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 InventoryCheckActionDtlMapper extends BaseMapper<InventoryCheckActionDtl> {

    @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.BOX_NOTE AS \"boxNote\",\n" +
            "  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" +
            "  INVCHKD.WAERS AS \"waers\",\n" +
            "  INVCHKD.DMBTR AS \"dmbtr\",\n" +
            "  WHS.STORAGE_NO AS \"storageNo\",\n" +
            "  INVCHKD.REMARKS AS \"remarks\"\n" +
            "FROM INVENTORY_CHECK_ACTION_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 INVENTORY_CHECK_ACTION_DTL \n" +
            "WHERE UUID= #{uuid}")
    int selectMaxTaskRowId(@Param("uuid") String uuid);


    @Select("SELECT CD.UUID AS \"uuid\",\n" +
            "       CD.TASK_ROW_ID AS \"taskRowId\",\n" +
            "       CD.MATERIAL_NO AS \"materialNo\",\n" +
            "       ABS(CD.DISCREPANCY) AS \"discrepancy\"," +
            "       CD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       CD.RELATE_ACCOUNT AS \"relateAccount\",\n" +
            "       C.COST_CENTER AS \"costCenter\",\n" +
            "       CD.VOUCHER_ITEM_TEXT AS \"voucherItemText\",\n" +
            "       C.WERKS AS \"werks\",\n" +
            "       C.WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
            "FROM INVENTORY_CHECK_ACTION_DTL CD\n" +
            "    LEFT JOIN INVENTORY_CHECK_ACTION C ON CD.UUID = C.UUID\n" +
            "WHERE C.UUID = #{uuid} AND CD.DISCREPANCY != 0 \n")
    List<Map<String, Object>> getInventoryActionDtlM(@Param("uuid") String uuid);

    @Select("SELECT ROWNUM * 10 AS \"rowNum\",\n" +
            "       BND.MATERIAL_NO AS \"materialNo\",\n" +
            "       BND.DOMESTIC_QTY_RECEIVED AS \"quantity\",\n" +
            "       BND.BASIC_UNIT AS \"basicUnit\",\n" +
            "       CD.RELATE_ACCOUNT AS \"relateAccount\",\n" +
            "       CD.TASK_ROW_ID AS \"taskRowId\",\n" +
            "       C.COST_CENTER AS \"costCenter\",\n" +
            "       CD.VOUCHER_ITEM_TEXT AS \"voucherItemText\",\n" +
            "       C.WERKS AS \"werks\",\n" +
            "       C.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       ABS(CD.DISCREPANCY) AS \"discrepancy\"\n" +
            "FROM INVENTORY_CHECK_ACTION_DTL CD\n" +
            "    LEFT JOIN INVENTORY_CHECK_ACTION C ON CD.UUID = C.UUID\n" +
            "    LEFT JOIN SAP_BOX_NOTE_DTL BND ON CD.BOX_NOTE = BND.BOX_NOTE\n" +
            "WHERE C.UUID = #{uuid} AND CD.DISCREPANCY != 0 \n")
    List<Map<String, Object>> getInventoryActionDtlX(@Param("uuid") String uuid);
}