package com.slcf.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import com.slcf.bean.LocDetailCondition;
import com.slcf.pojo.LocDetailBean;

@Repository
public interface LocDetailDao {

	/**
	 * 添加
	 * @param locDetail
	 * @return
	 */
	@Insert("insert into asr_loc_detl(loc_no,memo,modi_user,modi_time, "
			+ " matnr,lgnum,tbnum,tbpos,maktx,werks,anfme,altme,bname,zmatid,zpallet) "
			+ " values(#{loc_no},#{memo},#{modi_user},getdate(), "
			+ " #{matnr},#{lgnum},#{tbnum},#{tbpos},#{maktx},"
			+ " #{werks},#{anfme},#{altme},#{bname},#{zmatid},#{zpallet})")
	public int insertLocDetail(LocDetailBean locDetail);
	
	//分页查询所有信息
//	@Select("select top (#{epage}) * from asr_loc_detl where loc_no not in "
//	@Select("select top (#{epage}) a.*,b.loc_desc loc_sts_name from asr_loc_detl a,asr_bas_loc_type b where a.loc_sts=b.loc_sts and loc_no not in "
//			+ " (select top (#{spage}) loc_no from asr_loc_detl order by loc_no asc) order by loc_no asc")
//	public List<LocDetailBean> queryLocationList(@Param("spage")int spage,@Param("epage")int epage);
	public List<LocDetailBean> queryLocDetailList(LocDetailCondition locationCon);
	
	//统计统计库存总数量
	@Select("select sum(qty) from asr_loc_detl where mat_no=#{mat_no}")
	public int getLocStockCount(String mat_no);
	
	/**
	 * 不分页查询所有信息，用于excel导出 
	 * @param sysLogCon
	 * @return
	 */
	public List<LocDetailBean> getLocDetailAll(LocDetailCondition locationCon);
	
	//根据过滤条件统计总数量
	public int getLocDetailCount(LocDetailCondition locationCon);
	
	/**
	 * 查询所有
	 * @return
	 */
	@Select("select * from asr_loc_detl order by loc_no asc")
	public List<LocDetailBean> getLocDetailList();
	
	/**
	 * 根据id查询信息
	 * @param id
	 * @return
	 */
	@Select("select * from asr_loc_detl where loc_no=#{loc_no} and matnr=#{matnr}")
	public LocDetailBean getLocDetailById(@Param("loc_no")String loc_no,@Param("matnr")String matnr);
	
	//更新
	@Update("update asr_loc_detl set anfme=#{anfme},modi_time=getdate() "
			+ " where loc_no=#{loc_no} and matnr=#{matnr}")
	public int upLocDetail(LocDetailBean location);
	
	//根据id删除工作信息
	@Delete("delete from asr_loc_detl where loc_no=#{loc_no} and mat_no=#{mat_no}")
	public int delLocDetailById(@Param("loc_no")String loc_no,@Param("mat_no")String mat_no);
	
	//统计统计库存总数量
	@Select("select sum(qty) from asr_loc_detl where loc_no=#{loc_no}")
	public int getStockQtyByLoc(@Param("loc_no")String loc_no);
	
	//根据库位号获取库存明细
//	@Select("select a.*,b.barcode,c.mat_name from asr_loc_detl a,asr_loc_mast b,bas_mat_code c "
//			+ " where a.mat_no=c.mat_no and a.loc_no=b.loc_no and a.loc_no=#{loc_no} and b.loc_sts='F'")
	@Select("select a.loc_no,c.* from asr_loc_detl a,asr_loc_mast b,bas_mat_code c "
			+ " where a.mat_no=c.mat_no and a.loc_no=b.loc_no and a.loc_no=#{loc_no} and b.loc_sts='F'")
	public List<LocDetailBean> getLocDetlByLoc(@Param("loc_no")String loc_no);
	
	//根据条码获取库存明细
//	@Select("select a.*,c.mat_name from asr_loc_detl a,asr_loc_mast b,bas_mat_code c "
//			+ " where a.mat_no=c.mat_no and a.loc_no=b.loc_no and barcode=#{barcode} and b.loc_sts='F'")
	@Select("select a.loc_no,c.* from asr_loc_detl a,asr_loc_mast b,bas_mat_code c "
			+ " where a.mat_no=c.mat_no and a.loc_no=b.loc_no and b.barcode=#{barcode} and b.loc_sts='F'")
	public List<LocDetailBean> getLocDetlByBarcode(@Param("barcode")String barcode);
	
	//根据loc_no删除库存信息
	@Delete("delete from asr_loc_detl where loc_no=#{loc_no}")
	public int delLocDetailByLocNo(@Param("loc_no")String loc_no);
}