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 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 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 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 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); }