package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmsorder.entity.ShoppingScenarioDtl;
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-03-01
 */
@Mapper
@Component
public interface ShoppingScenarioDtlMapper extends BaseMapper<ShoppingScenarioDtl> {

    @Select("\n" +
            "SELECT SSD.UUID AS \"uuid\",\n" +
            "       SS.SCENARIO_ID AS \"scenarioId\",\n" +
            "       SSD.SSD_ROW_ID AS \"ssdRowId\",\n" +
            "       SSD.ROW_COMMENT AS \"rowComment\",\n" +
            "       SSD.MATERIAL_NO AS \"materialNo\",\n" +
//            "       SSD.QUANTITY AS \"quantity\",\n" +
            "       SSD.LONG_CONTRACT_CODE AS \"longContractCode\",\n" +
            "       SSD.BRAND AS \"brand\",\n" +
            "       SSD.EQUIPMENT_PARTS AS \"equipmentParts\",\n" +
            "      (CASE WHEN SI.SAFETY_QTY_BASIC_UNIT IS NULL THEN 0 ELSE SI.SAFETY_QTY_BASIC_UNIT END) AS \"safetyQtyBasicUnit\",\n" +
            "       SI.SAFETY_INVENTORY_YEAR AS \"safetyInventoryYear\",\n" +
            "       SI.STANDARD_CONSUMPTION_LAST_YEAR AS \"standardConsumptionLastYear\",\n" +
            "       SSD.SUPPLEMENT_QUANTITY AS \"supplementQuantity\",\n" +
            "       SSD.CENTRALIZED_PROCUREMENT AS \"centralizedProcurement\",\n" +
            "       SSD.STRATEGIC_GOODS AS \"strategicGoods\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       SAP.SUPPLY_DESC AS \"supplyDesc\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       SAP.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "       SRM.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "       SRM.SUPPLIER_NAME AS \"supplierName\",\n" +
            "       SRM.PROCUREMENT_ORGANIZATION AS \"procurementOrganization\",\n" +
            "       SRM.MIN_ORDER_QUANTITY AS \"minOrderQuantity\",\n" +
            "       SRM.MIN_PACK_QUANTITY AS \"minPackQuantity\",\n" +
            "       SRM.SOURCE_GOODS AS \"sourceGoods\",\n" +
            "       MPR.PRICE AS \"price\",\n" +
            "       MPR.CURRENCY AS \"currency\",\n" +
            "       mcl.RPTSTART_DATE AS \"prtStartDate\",\n" +
            "       mcl.RPTEND_DATE AS \"prtEndDate\",\n" +
            "       spms.CATEGORY_ID AS \"categoryId\",\n" +
            "      (CASE WHEN RE.QUANTITY IS NULL THEN 0 ELSE RE.QUANTITY END) AS \"inventoryQuantity\",\n" +
            "      (CASE WHEN WAY.WAY_QUANTITY IS NULL THEN 0 ELSE WAY.WAY_QUANTITY END) AS \"wayQuantity\",\n" +
            "      (CASE WHEN KUL.KUL_LABST366 IS NULL THEN 0 ELSE KUL.KUL_LABST366 END) AS \"labst366\"\n" +
            "FROM SHOPPING_SCENARIO_DTL SSD\n" +
            "    LEFT JOIN SHOPPING_SCENARIO SS ON SSD.UUID = SS.UUID\n" +
            "    LEFT JOIN (select MATERIAL_NO,max(CATEGORY_ID) as CATEGORY_ID from MATERIAL_SPMS GROUP BY MATERIAL_NO) spms ON SSD.MATERIAL_NO = spms.MATERIAL_NO\n" +
            "    LEFT JOIN MATERIAL_CATEGORY_LABOR mcl ON substr(spms.CATEGORY_ID,0,2) = mcl.CATEGORY_ID \n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON SSD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "    LEFT JOIN MATERIAL_PRICE MPR ON SSD.MATERIAL_NO = MPR.MATERIAL_NO\n" +
            "    LEFT JOIN MATERIAL_SRM_LONG_CONTRACT SRM ON SSD.LONG_CONTRACT_CODE = SRM.LONG_CONTRACT_CODE\n" +
            "    LEFT JOIN (SELECT * FROM SAFETY_INVENTORY WHERE SPMS_STATUS != '0' ) SI ON SSD.MATERIAL_NO = SI.MATERIAL_NO AND   SS.WERKS = SI.WERKS  \n" +
            "    LEFT JOIN (\n" +
            "        SELECT WS.WERKS, WSI.MATERIAL_NO, SUM(QUANTITY) AS \"QUANTITY\"\n" +
            "        FROM WHS_STORAGE_INVENTORY WSI\n" +
            "            LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "        GROUP BY WS.WERKS, WSI.MATERIAL_NO\n" +
            "        ) RE ON RE.WERKS = SS.WERKS AND RE.MATERIAL_NO = SSD.MATERIAL_NO\n" +
            "    LEFT JOIN (\n" +
            "        SELECT SHWERKS, MATNR, SUM(WAMNG) AS \"WAY_QUANTITY\"\n" +
            "        FROM Z_SPMS_ZAITU \n" +
            "        GROUP BY SHWERKS, MATNR\n" +
            "    ) WAY ON SS.WERKS = WAY.SHWERKS AND SSD.MATERIAL_NO = WAY.MATNR\n" +
            "    LEFT JOIN (\n" +
            "        SELECT WERKS, MATNR, SUM(LABST366)  AS \"KUL_LABST366\"\n" +
            "        FROM Z_SPMS_GET_KULING \n" +
            "        GROUP BY WERKS, MATNR\n" +
            "    ) KUL ON SS.WERKS = KUL.WERKS AND SSD.MATERIAL_NO = KUL.MATNR\n" +
            "WHERE SSD.UUID = #{uuid}\n" +
            "ORDER BY SSD.SSD_ROW_ID ASC")
    List<Map<String, Object>> getScenarioDtlList(@Param("uuid") String uuid);
}