package com.example.springboot.mapper;
|
import java.util.List;
|
|
import org.apache.ibatis.annotations.Delete;
|
import org.apache.ibatis.annotations.Insert;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Select;
|
import org.apache.ibatis.annotations.Update;
|
import org.springframework.stereotype.Repository;
|
import com.example.springboot.entity.GlassInfo;
|
import com.example.springboot.entity.StorageCage;
|
import com.example.springboot.entity.StorageTask;
|
|
@Mapper
|
@Repository
|
public interface AlbaniaMapper {
|
//判断笼内是否有合适的类型空格
|
@Select("select id from storage_cage where glasstype=#{glasstype} and width>=#{width}+#{widths} and disabled=0 order by id limit 1")
|
StorageCage SelectCage(int glasstype,Double width,int widths);
|
//判断笼内没有玻璃的空格
|
@Select("select id from storage_cage where (number is null or number=0) and cage>#{cage} and cage<#{cage2} and disabled=0 order by id limit 1")
|
StorageCage SelectNewCell(int cage,int cage2);
|
//增加玻璃数
|
@Update("update storage_cage set number=number+1,width=width-glasswidth where id=#{id}")
|
void UpdateCageNumberAdd(int id);
|
//减少玻璃数
|
@Update("update storage_cage set number=number-1,width=width+glasswidth where id=#{id}")
|
void UpdateCageNumberOut(int id);
|
//增加任务记录
|
@Insert("INSERT INTO `albania`.`storage_task`(`task_type`, `task_state`, `shelf_rack`, `load_rack`,start_time,glass_id,glasstype,flowcard,mateid,tier) VALUES (#{tasktype},#{taskstate},#{shelfrack},#{loadrack},now(),#{glassid},#{glasstype},#{flowcard},#{mateid},#{tier});")
|
void Inserttask(int tasktype, int taskstate, int shelfrack, int loadrack, String glassid, int glasstype,String flowcard,int mateid,int tier);
|
//增加代发任务记录
|
@Insert("INSERT INTO `albania`.`out_task`(`task_type`, `task_state`, `shelf_rack`, `load_rack`,start_time,glass_id,glasstype,flowcard,mateid,tier) VALUES (#{tasktype},#{taskstate},#{shelfrack},#{loadrack},now(),#{glassid},#{glasstype},#{flowcard},#{mateid},#{tier});")
|
void InsertOutTask(int tasktype, int taskstate, int shelfrack, int loadrack, String glassid, int glasstype,String flowcard,int mateid,int tier);
|
//新增一条笼子数据
|
@Update("UPDATE `albania`.`storage_cage` SET `glass_id` =#{glassid}, `width` =width-#{glasswidth}, `glasswidth` =#{glasswidth}, `glassheight` =#{glassheight}, `glasswidthmm` =#{glasswidth}, `glassheightmm` = #{glassheight}, `state` = #{state}, `glasstype` = #{glasstype}, `number` = #{number},thickness=#{thickness},films=#{films},flowcard=#{flowcard},mateid=#{mateid} WHERE `id` =#{id};")
|
void AddCage(int id,String glassid,Double glasswidth,Double glassheight,int state,int glasstype,int number,Double thickness,String films,String flowcard,int mateid);
|
//获取进片数据
|
@Select("select glassid,flowcard,glasswidth as width,glassheight as height,glasswidthmm,glassheightmm,glasstype,thickness from queue where state=1 limit 1")
|
GlassInfo SelectGlass();
|
//一号线查询任务
|
@Select("select * from v_cagerelease1 where mateid not in(select mateid from v_cagerelease1 where surplus=0 group by mateid);")
|
GlassInfo SelectOutGlass1();
|
//二号线查询任务
|
@Select("select * from v_cagerelease2 where mateid not in(select mateid from v_cagerelease2 where surplus=0 group by mateid)LIMIT 1;")
|
GlassInfo SelectOutGlass2();
|
//待出片任务查询
|
@Select("select * from out_task where task_state=0")
|
List<StorageTask> SelectOutTask();
|
//流程卡任务查询
|
@Select("select G.flowcard,G.glasstype,G.number,G.mateid,G.tier,G.starttime,G.finishnumber,IF((G.number-G.finishnumber-ku.KuCount)<0,G.number,IFNULL(ku.KuCount,0)) as 'surplus',G.totaltier as id,G.width,G.glassid from (select Gfd.flowcard,Ggf.glasstype,Ggf.number,Ggf.mateid,Ggf.tier,Ggf.finishnumber,Gfd.starttime,Gfd.totaltier,Ggf.width,Ggf.glassid from flowcard as Gfd LEFT JOIN glassinfo as Ggf on Gfd.flowcard=Ggf.flowcard where Gfd.state=1 and Gfd.line=1) as G left join (select glasstype,sum(number) AS KuCount from storage_cage where glasstype is not null and disabled=0 group by glasstype) as ku on G.glasstype=ku.glasstype where G.mateid not in(select mateid from v_cagerelease1 where surplus=0 group by mateid) HAVING surplus>0 order by `g`.`starttime`,`g`.`mateid`,`g`.`finishnumber`,`g`.`tier`LIMIT 1;")
|
GlassInfo SelectOutGlass(int line);
|
// @Select("select SQL_NO_CACHE G.flowcard,G.glasstype,G.number,G.mateid,G.tier,G.starttime,G.finishnumber,IF((G.number-G.finishnumber-ku.KuCount)<0,G.number,ku.KuCount) as 'surplus',G.totaltier as id,G.width,G.glassid from (select Gfd.flowcard,Ggf.glasstype,Ggf.number,Ggf.mateid,Ggf.tier,Ggf.finishnumber,Gfd.starttime,Gfd.totaltier,Ggf.width,Ggf.glassid from flowcard as Gfd LEFT JOIN glassinfo as Ggf on Gfd.flowcard=Ggf.flowcard where Gfd.state=1 and Gfd.line=#{line}) as G left join (select glasstype,sum(number) AS KuCount from storage_cage where flowcard is not null group by glasstype) as ku on G.glasstype=ku.glasstype where G.mateid not in(select mateid from v_cagerelease1 where surplus=0 group by mateid) HAVING surplus>0 order by `g`.`starttime`,`g`.`mateid`,`g`.`finishnumber`,`g`.`tier`LIMIT 1;")
|
// GlassInfo SelectOutGlass(int line);
|
//获取该类型在库存的位置
|
@Select("select * from storage_cage where glasstype=#{glasstype} and number>0 limit 1")
|
StorageCage SelectCageGlass(int glasstype);
|
//发送出片后增加已发数量
|
@Update("update glassinfo set finishnumber=ifnull(finishnumber,0)+1 where flowcard=#{flowcard} and mateid=#{mateid} and tier=#{tier};")
|
void AddFinishNumber(String flowcard,int mateid,int tier);
|
//获取当前任务的id
|
@Select("select id from storage_task where task_state=0 and glass_id=#{glassid} ORDER BY id desc LIMIT 1")
|
GlassInfo SelectTaskId(String glassid);
|
//获取当前未完成任务的数量
|
@Select("select count(*) from storage_task where task_state=0")
|
int SelectTaskcount();
|
//获取当前未完成任务的数量
|
@Select("select count(*) from out_task where task_state=0")
|
int SelectOutTaskCount();
|
//删除测量表的信息
|
@Delete("delete from queue where glassid=#{glassid}")
|
int DeleteQueue(String glassid);
|
//判断是否是直出模式
|
@Select("select method,flowcard from flowcard where state=1 and line=#{line} ORDER BY starttime LIMIT 1;")
|
GlassInfo SelectMethod(int line);
|
//直出模式任务查询
|
@Select("select b.flowcard,b.glassid,b.glasstype,b.mateid,b.width,b.tier,a.totaltier as id,IFNULL(c.number,0)as number,b.glassid from flowcard a left join glassinfo b on a.flowcard=b.flowcard left join(select sum(number)as number,glasstype from storage_cage GROUP BY glasstype)c on b.glasstype=c.glasstype where a.line=1 and c.number>0 and b.finishnumber<b.number ORDER BY b.finishnumber,b.tier asc limit 1")
|
GlassInfo SelectOutGlass2(int line);
|
//查询直通模式下最后一块
|
@Select("select IFNULL(b.tier, 0) from flowcard a left join glassinfo b on a.flowcard=b.flowcard left join(select sum(number)as number,glasstype from storage_cage GROUP BY glasstype)c on b.glasstype=c.glasstype where a.line=#{line} and c.number>0 ORDER BY b.tier desc LIMIT 1")
|
int SelectOutGlassend(int line);
|
//增加笼子表宽高
|
@Select("update storage_cage a set a.glasswidth=#{width},a.glassheight=#{height} where id=#{id} ")
|
void UpdateStorage(double width,double height,int id);
|
//更改队列表状态
|
@Update("update queue set state=2 where glassid=#{glassid};")
|
void UpdateQueueState(String glassid);
|
//更改队列表状态
|
@Select("select*from glassinfo where glasstype=#{glasstype} limit 1;")
|
GlassInfo SelectGlassinfo(Integer glasstype);
|
//查询纯配片模式可出配片id
|
@Select("select gls.flowcard,gls.mateid,gls.tier,gls.glasstype,kc.number from glassinfo as gls left join (select glasstype,sum(number) as number from storage_cage where number>0 and disabled=0 GROUP BY glasstype)as kc on gls.glasstype=kc.glasstype where gls.flowcard=#{flowcard} and gls.number>gls.finishnumber and kc.number>0 GROUP BY gls.flowcard, gls.mateid, gls.tier, gls.glasstype, kc.number,mateid order by gls.mateid;")
|
List<GlassInfo> SelectOutGlass3(String flowcard);
|
//判断此配片id是否有足够的玻璃
|
@Select("SELECT CASE WHEN storage.number >= required.number THEN '足够' ELSE '不足' END AS status FROM (SELECT a.glasstype, COUNT(a.glasstype) AS number FROM glassinfo a WHERE a.flowcard =#{flowcard} AND a.mateid =#{mateid} GROUP BY a.glasstype) AS required LEFT JOIN (SELECT a.glasstype, SUM(a.number) AS number FROM storage_cage a WHERE a.number > 0 and disabled=0 GROUP BY a.glasstype) AS storage ON required.glasstype = storage.glasstype;")
|
List<String> SelectOutGlass4(String flowcard,int mateid);
|
//查询此配片id需要的玻璃类型
|
@Select("select glasstype,width,sumtier from glassinfo where flowcard=#{flowcard} and mateid=#{mateid}")
|
List<GlassInfo> SelectOutGlass5(String flowcard,int mateid);
|
//判断此组玻璃是否能一次上车
|
@Select("select sum(width)+(sumtier-1)*300 as width,mateid from glassinfo where flowcard=#{flowcard} and mateid=#{mateid} GROUP BY mateid,sumtier")
|
GlassInfo SelectOutGlass6(String flowcard,int mateid);
|
//查询此配片id所需的类型
|
@Select("select * from glassinfo where flowcard=#{flowcard} and mateid=#{mateid}")
|
List<GlassInfo> SelectOutGlass7(String flowcard,int mateid);
|
//查询此配片id是否有可出片数量
|
@Select("SELECT CASE WHEN number >finishnumber THEN '足够' ELSE '不足' END AS status FROM glassinfo a WHERE a.flowcard =#{flowcard} AND a.mateid =#{mateid}")
|
List<String> SelectOutGlass8(String flowcard,int mateid);
|
}
|
