package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.SafetyInventorySearchDTO;
import com.sunda.spmsoversea.entity.SafetyInventory;
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 2022-01-04
 */
@Mapper
public interface SafetyInventoryMapper extends BaseMapper<SafetyInventory> {

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       SI.UUID AS \"uuid\",\n" +
            "       SI.WERKS \"werks\",\n" +
            "       SI.WHS_LOCATION_CODE \"whsLocationCode\",\n" +
            "       SI.MATERIAL_NO \"materialNo\",\n" +
            "       SI.BASIC_UNIT \"basicUnit\",\n" +
            "       MS.MATERIAL_ZH_DESC \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC \"materialEnDesc\",\n" +
            "       SI.SAFETY_QTY_BASIC_UNIT \"safetyQtyBasicUnit\",\n" +
            "       SI.SPMS_STATUS \"spmsStatus\",\n" +
            "       SI.STANDARD_CONSUMPTION_LAST_YEAR \"standardConsumptionLastYear\",\n" +
            "       SI.SAFETY_INVENTORY_YEAR \"safetyInventoryYear\",\n" +
            "       SI.EMERGENCY_RESERVE_BASIC_UNIT \"emergencyReserveBasicUnit\",\n" +
            "       SI.REMARKS \"remarks\",\n" +
            "       SI.COMMENTS \"comments\",\n" +
            "       SI.DATA_VERSION \"dataVersion\",\n" +
            "       SI.CREATE_USERID \"createUserid\",\n" +
            "       SU1.LAST_NAME \"createUsername\",\n" +
            "       SI.UPDATE_USERID \"updateUserid\",\n" +
            "       SU2.LAST_NAME \"updateUsername\",\n" +
            "       SI.CREATE_TIME \"createTime\",\n" +
            "       SI.CLASSIFY \"classify\",\n" +
            "       SI.UPDATE_TIME \"updateTime\"\n" +
            "FROM SAFETY_INVENTORY SI\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON SI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN SPMS_USER SU1 ON SI.CREATE_USERID = SU1.WORK_CODE\n" +
            "    LEFT JOIN SPMS_USER SU2 ON SI.UPDATE_USERID = SU2.WORK_CODE\n" +
            "WHERE SI.SPMS_STATUS != '0' " +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND SI.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.materialNo!=null and dto.materialNo != \"\"'> AND SI.MATERIAL_NO = #{dto.materialNo}</when>" +
            "  ORDER BY SI.MATERIAL_NO " +
            "</script>")
    List<Map<String, Object>> getSafetyInventoryPage(IPage page, @Param("dto") SafetyInventorySearchDTO dto);

    @Select("SELECT SI.UUID AS \"uuid\",\n" +
            "       SI.WERKS \"werks\",\n" +
            "       SI.WHS_LOCATION_CODE \"whsLocationCode\",\n" +
            "       SI.MATERIAL_NO \"materialNo\",\n" +
            "       SI.BASIC_UNIT \"basicUnit\",\n" +
            "       MS.MATERIAL_ZH_DESC \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC \"materialEnDesc\",\n" +
            "       SI.SAFETY_QTY_BASIC_UNIT \"safetyQtyBasicUnit\",\n" +
            "       SI.SPMS_STATUS \"spmsStatus\",\n" +
            "       SI.STANDARD_CONSUMPTION_LAST_YEAR \"standardConsumptionLastYear\",\n" +
            "       SI.SAFETY_INVENTORY_YEAR \"safetyInventoryYear\",\n" +
            "       SI.EMERGENCY_RESERVE_BASIC_UNIT \"emergencyReserveBasicUnit\",\n" +
            "       SI.REMARKS \"remarks\",\n" +
            "       SI.COMMENTS \"comments\",\n" +
            "       SI.DATA_VERSION \"dataVersion\",\n" +
            "       SI.CREATE_USERID \"createUserid\",\n" +
            "       SU1.LAST_NAME \"createUsername\",\n" +
            "       SI.UPDATE_USERID \"updateUserid\",\n" +
            "       SU2.LAST_NAME \"updateUsername\",\n" +
            "       SI.CREATE_TIME \"createTime\",\n" +
            "       SI.CLASSIFY \"classify\",\n" +
            "       SI.UPDATE_TIME \"updateTime\"\n" +
            "FROM SAFETY_INVENTORY SI\n" +
            "         LEFT JOIN MATERIAL_SAP MS ON SI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "         LEFT JOIN SPMS_USER SU1 ON SI.CREATE_USERID = SU1.WORK_CODE\n" +
            "         LEFT JOIN SPMS_USER SU2 ON SI.UPDATE_USERID = SU2.WORK_CODE\n" +
            "WHERE SI.UUID = #{uuid} AND SI.SPMS_STATUS != '0' ")
    Map<String, Object> getSafetyInventoryInfo(@Param("uuid") String uuid);

    @Select("" +
            "SELECT OWOD.WERKS AS \"werks\",\n" +
            "       OWOD.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWOD.MATERIAL_NO AS \"materialNo\",\n" +
            "       SUM(OWOD.ACTUAL_QTY_BASIC_UNIT) AS \"standardConsumptionLastYear\", \n" +
            "       MA.MATERIAL_ZH_DESC AS \"materialZhDesc\", \n" +
            "       MA.MATERIAL_EN_DESC AS \"materialEnDesc\", \n" +
            "       MA.BASIC_UNIT AS \"basicUnit\"\n" +
            "FROM OVERSEA_WHS_OUT_DTL OWOD\n" +
            "    LEFT JOIN OVERSEA_WHS_OUT OWO ON OWOD.UUID_WHS_OUT = OWO.UUID_WHS_OUT \n" +
            "    LEFT JOIN MATERIAL_SAP MA ON OWOD.MATERIAL_NO = MA.MATERIAL_NO\n" +
            "WHERE OWO.SPMS_STATUS != '0'\n" +
            "  AND OWOD.ACTUAL_QTY_BASIC_UNIT != 0\n" +
            "  AND OWOD.WERKS = #{werks}\n" +
            "  AND OWOD.MATERIAL_NO = #{materialNo}\n" +
            "  AND SUBSTR(OWO.CREATE_DATE, 0, 4) = TO_CHAR(#{yearNumber})\n" +
            "GROUP BY OWOD.WERKS, OWOD.WHS_LOCATION_CODE, OWOD.MATERIAL_NO, MA.MATERIAL_ZH_DESC, MA.MATERIAL_EN_DESC, MA.BASIC_UNIT" +
            "")
    Map<String, Object> getStandardConsumption(@Param("werks") String werks,
                                               @Param("materialNo") String materialNo,
                                               @Param("yearNumber") Integer yearNumber);

    @Select("SELECT ROWNUM AS \"rowId\",\n" +
            "       SI.WERKS AS \"werks\",\n" +
            "       SI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       SI.MATERIAL_NO AS \"materialNo\",\n" +
            "       SI.SAFETY_QTY_BASIC_UNIT AS \"safetyQtyBasicUnit\",\n" +
            "       SI.REMARKS \"remarks\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       RE.QUANTITY AS \"quantity\"\n" +
            "FROM SAFETY_INVENTORY SI\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON SI.MATERIAL_NO = MS.MATERIAL_NO\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" +
            "        WHERE WS.WERKS = #{werks}\n" +
            "        GROUP BY WS.WERKS, WSI.MATERIAL_NO\n" +
            "    ) RE ON SI.WERKS = RE.WERKS AND SI.MATERIAL_NO = RE.MATERIAL_NO\n" +
            "WHERE SI.WERKS = #{werks} AND RE.QUANTITY < SI.SAFETY_QTY_BASIC_UNIT ")
    /** 获取指定工厂下，有安全库存数量信息 且 对应物料库存数量 低于 安全库存数量的数据 */
    List<Map<String, Object>> getBelowSafetyInventory(@Param("werks") String werks);


    @Select("<script>" +
            "SELECT SI.WERKS AS \"werks\",\n" +
            "       SI.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       SI.CLASSIFY AS \"classify\",\n" +
            "       SI.SAFETY_QTY_BASIC_UNIT AS \"safetyQtyBasicUnit\",\n" +
            "       SI.STANDARD_CONSUMPTION_LAST_YEAR AS \"standardConsumptionLastYear\",\n" +
            "       SI.SAFETY_INVENTORY_YEAR AS \"safetyInventoryYear\",\n" +
            "       SI.EMERGENCY_RESERVE_BASIC_UNIT AS \"emergencyReserveBasicUnit\",\n" +
            "       SI.REMARKS AS \"remarks\",\n" +
            "       SI.UPDATE_TIME AS \"updateTime\",\n" +
            "       SI.BASIC_UNIT AS \"basicUnit\"\n" +
            "FROM SAFETY_INVENTORY SI\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON SI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "WHERE 1 = 1 \n" +
            " <when test='werks!=null and werks != \"\"'> AND SI.WERKS = #{werks}</when>"+
            " <when test='materialNo!=null and materialNo != \"\"'> AND SI.MATERIAL_NO = #{materialNo}</when>"+
            "</script>")
    /**    获取安全库存报表*/
    List<Map<String, Object>> exportSafetyInventoryDtl(@Param("werks") String werks,
                                                       @Param("materialNo") String materialNo);
}