package com.prolog.eis.dao.out.shelves;


import com.prolog.eis.model.inbound.BizInboundTaskDetail;
import com.prolog.eis.model.shelves.PalletLocation;
import com.prolog.eis.vo.GoodsInfoVO;
import com.prolog.eis.vo.GoodsShelvesVO;
import com.prolog.framework.dao.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface BizInboundTaskDetailMapper extends BaseMapper<BizInboundTaskDetail> {

    @Select("<script>SELECT b.item_id itemId from biz_inbound_task a  LEFT JOIN biz_inbound_task_detail b\n" +
            " on a.id = b.inbound_id " +
            " where a.container_no =#{containerNo}  and a.status <![CDATA[<]]>  20 " +
            " and ifnull(b.is_hang_up,0) <![CDATA[<>]]> 1 " +
            " and ifnull(b.detail_status,0) <![CDATA[<]]> 20 and b.qty-IFNULL(b.real_qty,0) <![CDATA[>]]> 0 and " +
            " a.wms_location_code =  #{wmsLocationCode} and a.container_no = #{containerNo} group by b.item_id</script>")
    List<String> findInboundTask(@Param("containerNo") String containerNo, @Param("wmsLocationCode") String wmsLocationCode);

    @Select("<script>SELECT a.pallet_no palletNo,a.angle,b.abc_type abcType,b.pallet_location palletLocation," +
            " b.planes,IFNULL(a.pallet_type,0)  palletType,c.item_id itemId,c.lot_id lotId ,c.owner_id ownerId,IFNULL(c.qty,0) qty," +
            " c.qty,ifnull(c.single_volume,0) singleVolume,ifnull(b.volume,0) volume,ifnull(b.volume,0) - ifnull(c.volume,0) availableVolume ,2 locationType\n" +
            " from pallet_info a left join pallet_location b \n" +
            " on a.pallet_no = b.pallet_no LEFT JOIN container_info_detail c \n" +
            " on c.container_sub_no = b.pallet_location" +
            " where ifnull(a.is_lock,0) = 0 and ifnull(b.is_lock,0) = 0 and  ifnull(a.container_move_task_id,0) = 0" +
            " and ifnull(c.qty,0) <![CDATA[*]]>  ifnull(c.single_volume,0) <![CDATA[<]]> b.volume" +
            " and a.status = 0  " +
            "<if test=\"subArea!=null \"> " +
            " and a.sub_area =  #{subArea}" +
            "</if> " +
            "<if test=\"abcTypes!=null and abcTypes.size() > 0 \"> " +
            " and b.abc_type in " +
            " <foreach collection='abcTypes' item='abcType' open='(' separator=',' close=')'>\n" +
            "  #{abcType}\n" +
            " </foreach>\n" +
            "</if> " +
            "<if test=\"palletNos!=null and palletNos.size() > 0 \"> " +
            " and a.pallet_no in " +
            " <foreach collection='palletNos' item='palletNo' open='(' separator=',' close=')'>\n" +
            "  #{palletNo}\n" +
            " </foreach>\n" +
            "</if> " +
            "<if test=\"palletTypes!=null and palletTypes.size() > 0 \"> " +
            " and (a.pallet_type in " +
            " <foreach collection='palletTypes' item='palletType' open='(' separator=',' close=')'>\n" +
            "  #{palletType}\n" +
            " </foreach>\n" +
            " or  ifnull(a.pallet_type,0) = 0)" +
            "</if> " +
            " ORDER BY ifnull(b.volume,0) - ifnull(c.volume,0) ,b.pallet_no asc,b.pallet_location asc " +
            "</script>")
    List<GoodsShelvesVO> findPalletNo(@Param("palletNos") List<String> palletNos, @Param("palletTypes") List<String> palletTypes, @Param("abcTypes") List<String> abcTypes, @Param("bizPropertyId") Integer bizPropertyId, @Param("subArea") String subArea);


    /*@Select("<script> " +
            " SELECT a.pallet_location palletLocation,a.pallet_no palletNo,a.planes,b.qty,b.item_id goodsId,\n" +
            " b.lot_id lotId,b.pack_id packId, b.owner_id ownerId\n" +
            " FROM pallet_location a LEFT JOIN container_info_detail b \n" +
            " on a.pallet_location = b.container_sub_no \n" +
            " where a.pallet_no = #{palletNo}" +
            " and (ifnull(b.item_id,0) = 0 or b.item_id = #{itemId})" +
            " and (ifnull(b.lot_id,0) = 0 or b.lot_id = #{lotId})" +
            " and (ifnull(b.pack_id,0) = 0 or b.pack_id = #{packId})" +
            " and (ifnull(b.owner_id,0) = 0 or b.owner_id = #{ownerId})" +
            "order by a.pallet_location asc" +
            "</script>")*/
    List<GoodsInfoVO> findNowPalletNo(@Param("palletNo") String palletNo, @Param("itemId") String itemId,@Param("lotId") String lotId,@Param("packId") String packId,@Param("ownerId") String ownerId);


    List<GoodsInfoVO> findNewPalletNo(@Param("palletNo") String palletNo, @Param("palletLocation") String palletLocation, @Param("stationArea") String stationArea, @Param("areaNo") String areaNo, @Param("itemId") String itemId, @Param("lotId") String lotId,@Param("ownerId") String ownerId,@Param("itemHealthCategory") String itemHealthCategory,@Param("locationType") String locationType,@Param("abcType")String abcType);

    List<PalletLocation> findLocation(@Param("palletLocation") String palletLocation);


    @Select("<script>" +
            " SELECT COUNT(1)\n" +
            " from pallet_location a LEFT JOIN container_info_detail b \n" +
            " on a.pallet_location = b.container_sub_no\n" +
            " where\n" +
            " ((a.abc_type = #{abcType} and a.pallet_location = #{palletLocation}) or b.container_sub_no = #{palletLocation})\n" +
            " and a.pallet_no = #{palletNo} " +
            "</script>")
    int queryConfirmABC(@Param("abcType") String abcType, @Param("bizPropertyId") Long bizPropertyId, @Param("palletNo") String palletNo, @Param("palletLocation") String palletLocation);


    @Select("<script>" +
            " SELECT d.container_sub_no palletLocation,x.angle,d.item_id itemId,d.lot_id lotId ,d.owner_id ownerId,e.planes,a.container_no from biz_inbound_task_detail b\n" +
            " LEFT JOIN container_info_detail d on d.item_id  = b.item_id  and d.lot_id  = b.lot_id and d.owner_id  = b.owner_id \n" +
            " LEFT JOIN pallet_location e on e.pallet_location = d.container_sub_no\n" +
            " LEFT JOIN biz_inbound_task a on a.id = b.inbound_id\n" +
            " LEFT JOIN pallet_info x on d.container_no=x.pallet_no\n" +
            " where a.status <![CDATA[<]]> 20  \n" +
            " and  d.container_sub_no  is not null  \n" +
            " and  e.planes is not null  \n" +
            " and ifnull(b.is_hang_up,0) <![CDATA[<>]]> 1 and ifnull(b.detail_status,0) <![CDATA[<]]> 20 \n" +
            " and  a.wms_location_code =  #{wmsLocationCode} " +
            "<if test=\"stationId!=null \"> " +
            " and b.station_id = #{stationId} " +
            "</if> " +
            "</script>")
    List<GoodsShelvesVO> findCacheTasks(@Param("stationId") Integer stationId, @Param("wmsLocationCode") String wmsLocationCode);
}