package com.mandi.fendan.mapper;

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 com.mandi.fendan.persist.Fd_Bsheet;
import com.mandi.fendan.persist.Fd_XSTD;
import com.mandi.fendan.util.persist.Serviceenum;
import com.mandi.fendan.util.persist.WaitXSTD;
import com.mandi.fendan.util.persist.WaitXSTDMX;
import com.mandi.fendan.util.persist.Yktbsitem;



public interface Fd_BsheetMapper {
	@Select("select a.* from fd_bsheet a where a.id=#{id}")
	public Fd_Bsheet get(String id);
	@Select("select * from fd_bsheet where fdsheetNo=#{fdsheetNo} ")
	public Fd_Bsheet getbyfdNo(String fdsheetNo);
	@Select("select * from fd_bsheet where bsNo=#{bsNo} ")
	public Fd_Bsheet getbybsNo(String bsNo);
	@Select("select top 1 * from fd_bsheet where vehicleId=#{vehicleId} and sdepartNo=#{sdepartNo} and status='在办' ")
	public Fd_Bsheet getbyVehicle(@Param("vehicleId")String vehicleId,@Param("sdepartNo")String sdepartNo);
	@Select("select top 1 * from fd_bsheet where vehicleId=#{vehicleId} and sdepartNo!=#{sdepartNo} and status='在办' ")
	public Fd_Bsheet getbyVehicleWithNoSdepartNo(@Param("vehicleId")String vehicleId,@Param("sdepartNo")String sdepartNo);

	@Delete("delete  from fd_bsheet where id=#{id}")
	public int del(String id);
	@Delete("delete  from fd_bsheet where fdsheetNo=#{fdsheetNo}")
	public int delbyfdNo(String fdsheetNo);
	@Delete("delete  from fd_bsheet where bsNo=#{bsNo} ")
	public int delbybsNo(String bsNo);
	@Delete("delete from bussinessitems where pici=#{pici} and bussinessNo=#{bussinessNo}")
	public int delbsitem(@Param("pici")String pici,@Param("bussinessNo") String bussinessNo);

	@Update("update fd_bsheet set shdw=#{shdw} where id=#{id} ")
	public int updateShdw(@Param("id") String id,@Param("shdw")String shdw);
	@Update("update fd_bsheet set companyName=#{companyName},companyNo=#{companyNo},createName=#{createName},createid=#{createid},ctime=#{ctime},driver=#{driver},fdsheetNo=#{fdsheetNo},phone=#{phone},"
			+ "vehicleId=#{vehicleId},bsNo=#{bsNo},status=#{status},sendmsg=#{sendmsg},updateTime=#{updateTime} where id=#{id}")
	public int update(Fd_Bsheet fd_bsheet);
	@Update("update fd_bsheet set invitecode=#{invitecode},sendmsg=#{sendmsg} where id=#{id}")
	public int updateInvitaCode(Fd_Bsheet fd_bsheet);
	@Insert("insert into fd_bsheet(id,companyName,companyNo,createName,createid,ctime,driver,fdsheetNo,phone,"
			+ "vehicleId,bsNo,status,sendmsg,sdepartNo,updateTime) values (#{id},#{companyName},#{companyNo},#{createName},"
			+ "#{createid},#{ctime},#{driver},#{fdsheetNo},#{phone},upper(#{vehicleId}),#{bsNo},#{status},#{sendmsg},#{sdepartNo},#{updateTime})")
	public int insert(Fd_Bsheet fd_bsheet);
	@Insert("insert into fd_bsheet(id,companyName,companyNo,createName,createid,ctime,driver,fdsheetNo,phone,"
			+ "vehicleId,bsNo,status,sendmsg,sdepartNo,shdw,updateTime) values (#{id},#{companyName},#{companyNo},#{createName},"
			+ "#{createid},#{ctime},#{driver},#{fdsheetNo},#{phone},upper(#{vehicleId}),#{bsNo},#{status},#{sendmsg},#{sdepartNo},#{shdw},#{updateTime})")
	public int insertAll(Fd_Bsheet fd_bsheet);
	@Update("update fd_bsheet set status=#{status} where id=#{id}")
	public int updateStatus(@Param("status")Serviceenum status,@Param("id")String id);
	@Update("update fd_bsheet set sdepartNo=#{sdepartNo} where id=#{id} ")
	public int updatesdepartNo(@Param("id") String id,@Param("sdepartNo")String sdepartNo);


	@Select("select top 1  * from fd_bsheet where vehicleId=#{vehicleId} and status=#{status} and sdepartNo=#{sdepartNo} ")
	public Fd_Bsheet getbyVehicleId(@Param("vehicleId") String vehicleId,@Param("status") Serviceenum status,@Param("sdepartNo")String sdepartNo);
	@Select("select * from fd_bsheet where vehicleId=#{vehicleId} and status=#{status} ")
	public List<Fd_Bsheet> getListbyVehicleId(@Param("vehicleId") String vehicleId,@Param("status") Serviceenum status);

	@Select("select CASE WHEN a.td_fpbh IS NULL THEN a.BB WHEN a.td_fpbh='' THEN a.BB ELSE a.AA END td_khmc,"
			+ "a.td_khbh td_bmbh,a.id td_shdd from ( "
			+ "SELECT c.td_fpbh,c.td_khbh,d.id,"
			+ "CASE WHEN c.td_ywdybh= '101' THEN '河南报关' WHEN c.td_ywdybh= '102' THEN '郑州报关' WHEN c.td_ywdybh= '111' THEN '明晟报关' WHEN c.td_ywdybh= '114' THEN '义瑞报关' ELSE '科技报关' END AA,"
			+ "d.name BB from fd_bsheet a left JOIN fd_vehicle_xstd b on a.bsNo=b.bsNo and a.vehicleId=b.vehicleId "
			+ "left join fd_xstd c on b.xstdNo=c.td_tdbh "
			+ "LEFT JOIN company d on c.td_khbh=d.companyNo where a.bsNo=#{bsNo} and a.id=#{id} and b.fd_num>0 ) a ")
	public List<WaitXSTD> getAllKH(@Param("bsNo") String bsNo,@Param("id") String id);
//	@Select("select a.* from fd_bsheet a LEFT JOIN cardinuse b on a.vehicleId=b.numberplates where a.sendmsg=0 "
//			+ "and a.companyNo=#{companyNo} and a.status=#{status} "
//			+ " and (b.cardno ='' or b.cardno is null )  and a.sdepartNo=#{sdepartNo} ")
//	public List<Fd_Bsheet> getSendMsglistbycNo(@Param("status")Serviceenum status,@Param("companyNo")String companyNo,@Param("sdepartNo")String sdepartNo);
//	@Select("select a.* from fd_bsheet a LEFT JOIN cardinuse b on a.vehicleId=b.numberplates where a.sendmsg=0 and a.createid=#{createid} and a.status=#{status}  and (b.cardno ='' or b.cardno is null ) ")
//	public List<Fd_Bsheet> getSendMsglistbycId(@Param("status")Serviceenum status,@Param("createid")String createid);


	@Insert("insert into bussinessitems "
	+ "(id,bussinessNo,name,materielNo,norms,unitname,num,note,weight,price,preweight,pici,hejin,gross,taotong,taotongWeight,money,"
	+ "createid,cname,cdepartid,cdepart,ctime,loadbuild,factory,fachang,fcnum,product_billNo,product_serialNo,product_otherNo,"
	+ "product_ywrq,product_flh,reserved11,reserved12,reserved13,reserved14,reserved15,reserved16,recompany,reID,"
	+ "decompany,deID,rcompany,rID,frombatch,jsnum,forshowindx,barcode,nzwzfinish,product_thlx,"
	+ "product_thyy,product_thdq) values (#{id},#{bussinessNo},#{name},#{materielNo},#{norms},"
	+ "#{unitname},#{num},#{note},#{weight},#{price},#{preweight},#{pici},#{hejin},#{gross},"
	+ "#{taotong},#{taotongWeight},#{money},#{createid},#{cname},#{cdepartid},#{cdepart},"
	+ "#{ctime},#{loadbuild},#{factory},#{fachang},#{fcnum},#{product_billNo},#{product_serialNo},"
	+ "#{product_otherNo},#{product_ywrq},#{product_flh},#{reserved11},#{reserved12},#{reserved13},"
	+ "#{reserved14},#{reserved15},#{reserved16},#{recompany},#{reID},#{decompany},#{deID},#{rcompany},#{rID},"
	+ "#{frombatch},#{jsnum},#{forshowindx},#{barcode},#{nzwzfinish},#{product_thlx},#{product_thyy},#{product_thdq})")
	public int insertbsitem( Yktbsitem bsitem);
	@Select("select * from bussinessitems where pici=#{pici} and bussinessNo=#{bussinessNo}")
	public Yktbsitem getbsitem(@Param("pici")String pici,@Param("bussinessNo") String bussinessNo);
	@Select("select count(1) from bussinessitems where bussinessNo=#{bussinessNo}")
	public int getbsitembyNo(@Param("bussinessNo") String bussinessNo);

	@Select("select * from xstdmx_view where tdmx_tdls=#{tdmx_tdls} and tdmx_tdfl=#{tdmx_tdfl} ")
	public WaitXSTDMX getWaittdmx(@Param("tdmx_tdls") String tdls,@Param("tdmx_tdfl") String tdfl);




}