package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.KulingReport;
import com.sunda.spmsoversea.dto.SpmsKulingReportExcel;
import com.sunda.spmsoversea.entity.ZSpmsGetKuling;
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>
 * SAP呆滞库存 Mapper 接口
 * </p>
 *
 * @author HZC
 * @since 2024-01-31
 */
@Mapper
@Component
public interface ZSpmsGetKulingMapper extends BaseMapper<ZSpmsGetKuling> {


    @Select("<script>" +
            "SELECT werks,lgort,matnr,maktx,meins,erfme,labst,erfmg,dmbtr,waers,erfmg30,erfmg60,erfmg90,erfmg120," +
            "erfmg150,erfmg180,erfmg210,erfmg240,erfmg270,erfmg300,erfmg330,erfmg365,erfmg366,labst30,labst60,labst90," +
            "labst120,labst150,labst180,labst210,labst240,labst270,labst300,labst330,labst365,labst366,dmbtr30,dmbtr60," +
            "dmbtr90,dmbtr120,dmbtr150,dmbtr180,dmbtr210,dmbtr240,dmbtr270,dmbtr300,dmbtr330,dmbtr365,dmbtr366,prdha,vtext \n" +
            "FROM Z_SPMS_GET_KULING \n" +
            "WHERE  MATNR in (\n" +
            " SELECT MATERIAL_NO FROM WHS_STORAGE_INVENTORY wsi \n" +
            " inner join  WHS_STORAGE ws on ws.uuid = wsi.uuid \n" +
            " WHERE wsi.QUANTITY > 0 AND wsi.MATERIAL_NO IS NOT NULL \n" +
            " and ws.WERKS = #{dto.werks} \n" +
            ") \n" +
            " <when test='dto.labst!=null and dto.labst == \"30\" '> AND labst30 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"60\" '> AND labst60 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"90\" '> AND labst90 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"120\" '> AND labst120 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"180\" '> AND labst180 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"365\" '> AND labst365 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"366\" '> AND labst366 > 0 </when>" +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND werks = #{dto.werks}</when>" +
            " <when test='dto.lgort!=null and dto.lgort != \"\"'> AND lgort = #{dto.lgort}</when>" +
            " <when test='dto.matnr!=null and dto.matnr != \"\"'> AND matnr = #{dto.matnr}</when>" +
            "</script>")
    List<ZSpmsGetKuling> getKuling(IPage iPage, @Param("dto")KulingReport dto);


    @Select("select ws.WHS_LOCATION_CODE from WHS_STORAGE_INVENTORY  wsi \n" +
            "left join WHS_STORAGE ws on wsi.uuid = ws.uuid\n" +
            "where ws.WERKS =#{werks} AND wsi.MATERIAL_NO = #{materialNo} ")
    List<Map<String, Object>> getWhsStorageNo(@Param("werks") String werks, @Param("materialNo") String materialNo);


    @Select("<script>" +
            "SELECT werks,lgort,matnr,maktx,meins,erfme,labst,erfmg,dmbtr,waers,erfmg30,erfmg60,erfmg90,erfmg120," +
            "erfmg150,erfmg180,erfmg210,erfmg240,erfmg270,erfmg300,erfmg330,erfmg365,erfmg366,labst30,labst60,labst90," +
            "labst120,labst150,labst180,labst210,labst240,labst270,labst300,labst330,labst365,labst366,dmbtr30,dmbtr60," +
            "dmbtr90,dmbtr120,dmbtr150,dmbtr180,dmbtr210,dmbtr240,dmbtr270,dmbtr300,dmbtr330,dmbtr365,dmbtr366,prdha,vtext \n" +
            "FROM Z_SPMS_GET_KULING \n" +
            "WHERE  MATNR in (\n" +
            " SELECT MATERIAL_NO FROM WHS_STORAGE_INVENTORY wsi \n" +
            " inner join  WHS_STORAGE ws on ws.uuid = wsi.uuid \n" +
            " WHERE wsi.QUANTITY > 0 AND wsi.MATERIAL_NO IS NOT NULL \n" +
            " and ws.WERKS = #{dto.werks} \n" +
            ") \n" +
            " <when test='dto.labst!=null and dto.labst == \"30\" '> AND labst30 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"60\" '> AND labst60 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"90\" '> AND labst90 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"120\" '> AND labst120 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"180\" '> AND labst180 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"365\" '> AND labst365 > 0 </when>" +
            " <when test='dto.labst!=null and dto.labst == \"366\" '> AND labst366 > 0 </when>" +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND werks = #{dto.werks}</when>" +
            " <when test='dto.lgort!=null and dto.lgort != \"\"'> AND lgort = #{dto.lgort}</when>" +
            " <when test='dto.matnr!=null and dto.matnr != \"\"'> AND matnr = #{dto.matnr}</when>" +
            "</script>")
    List<SpmsKulingReportExcel> getKulingExcel(@Param("dto")KulingReport dto);


}