package com.sunda.spmsoversea.mapper;

import com.sunda.spmsoversea.dto.OverseaInvCheckSearchDTO;
import com.sunda.spmsoversea.entity.OverseaInvCheck;
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 OverseaInvCheckMapper extends BaseMapper<OverseaInvCheck> {

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "    SELECT OIC.UUID AS \"uuid\",\n" +
            "        OIC.INVENTORY_CHECK_ID AS \"inventoryCheckId\",\n" +
            "        OIC.WERKS AS \"werks\",\n" +
            "        OIC.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "        OIC.INVENTORY_CHECK_CATEGORY AS \"inventoryCheckCategory\",\n" +
            "        OIC.INVENTORY_CHECK_MOLD AS \"inventoryCheckMold\",\n" +
            "        TO_CHAR(TO_DATE(OIC.CREATE_DATE, 'yyyymmdd'), 'yyyy-mm-dd') AS \"createDate\",\n" +
            "        OIC.INVENTORY_CHECK_SURPLUS AS \"inventoryCheckSupplus\",\n" +
            "        OIC.INVENTORY_CHECK_LOSS AS \"inventoryCheckLost\",\n" +
            "        OIC.INVENTORY_CHECK_RESULT AS \"inventoryCheckResult\",\n" +
            "        OIC.IS_REVIEW AS \"isReview\",\n" +
            "        OIC.SPMS_STATUS AS \"spmsStatus\",\n" +
            "        OIC.REQUESTER AS \"createUserid\",\n" +
            "        SU.LAST_NAME AS \"createUsername\",\n" +
            "        OICD.TASK_ROW_ID AS \"taskRowId\",\n" +
            "        OICD.MATERIAL_NO AS \"materialNo\",\n" +
            "        OICD.INVENTORY_QUANTITY AS \"inventoryQuantity\",\n" +
            "        OICD.CHECK_QUANTITY AS \"checkQuantity\",\n" +
            "        OICD.DISCREPANCY AS \"discrepancy\",\n" +
            "        MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "        MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "        MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "        OICD.STORAGE_UUID AS \"storageUuid\",\n" +
            "        WS.STORAGE_AREA AS \"storageArea\",\n" +
            "        WS.STORAGE_NO AS \"storageNo\"\n" +
            "    FROM OVERSEA_INV_CHECK OIC\n" +
            "        LEFT JOIN OVERSEA_INV_CHECK_DTL OICD ON OIC.UUID = OICD.UUID\n" +
            "        LEFT JOIN SPMS_USER SU ON OIC.REQUESTER = SU.WORK_CODE\n" +
            "        LEFT JOIN MATERIAL_SAP MS ON OICD.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "        LEFT JOIN WHS_STORAGE WS ON OICD.STORAGE_UUID = WS.UUID\n" +
            "    WHERE OIC.WERKS != 'CN01'\n" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND OIC.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND OIC.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            "  <when test='dto.inventoryCheckCategory!=null and dto.inventoryCheckCategory != \"\"'> AND OIC.INVENTORY_CHECK_CATEGORY = #{dto.inventoryCheckCategory}</when>" +
            "  <when test='dto.inventoryCheckMold!=null and dto.inventoryCheckMold != \"\"'> AND OIC.INVENTORY_CHECK_MOLD = #{dto.inventoryCheckMold}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OIC.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.endDate!=null and dto.endDate != \"\"'> AND TO_DATE(OIC.CREATE_DATE, 'yyyymmdd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-mm-dd')</when>\n" +
            "  <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(OIC.CREATE_DATE, 'yyyymmdd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-mm-dd')</when>\n" +
            "  <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            "    ORDER BY TO_DATE(OIC.CREATE_DATE, 'yyyymmdd') DESC, TO_NUMBER(OICD.TASK_ROW_ID)\n" +
            "    ) RE" +
            "</script>")
    /** 获取海外仓盘点任务，带明细不分页报表 */
    List<Map<String, Object>> getOverseaInvCheck(@Param("dto") OverseaInvCheckSearchDTO dto);

}
