82 lines
3.1 KiB
SQL
82 lines
3.1 KiB
SQL
drop table if exists cutsquarestepyield;
|
|
create table cutsquarestepyield
|
|
(
|
|
step varchar(50) comment '工序',
|
|
crystalbarno varchar(100) comment '晶编',
|
|
subprod varchar(50),
|
|
length int comment '长度',
|
|
weight decimal(10, 3) comment '重量',
|
|
eqpid varchar(100) comment '设备编号',
|
|
prodname varchar(100) comment '物料编码',
|
|
createtime datetime comment '创建时间',
|
|
mfgdate datetime comment '计产时间'
|
|
);
|
|
##截断
|
|
replace into cutsquarestepyield(step, crystalbarno, eqpid, prodname, createtime, mfgdate,subprod)
|
|
select 'cut' step,
|
|
crystal_code,
|
|
machine_no,
|
|
product_code prodname,
|
|
update_time,
|
|
date_format(date_sub(update_time, interval 510 minute), '%Y-%m-%d') mfgdate,
|
|
case when LOCATE('自产',product_name) >0 then mid(product_name,LOCATE('自产',product_name)+3,6)
|
|
when LOCATE('代工',product_name) >0 then mid(product_name,LOCATE('代工',product_name)+3,6) end subprod
|
|
from mes_sync.mes_truncation_task
|
|
where 1 = 1
|
|
and status = '2'
|
|
and crystal_code like '%W'
|
|
or crystal_code like '%N';
|
|
##开方
|
|
replace into cutsquarestepyield(step, crystalbarno, eqpid, prodname, createtime, mfgdate,subprod)
|
|
select 'square' step,
|
|
round_bar_code,
|
|
material_code,
|
|
device_code,
|
|
update_time,
|
|
date_format(date_sub(update_time, interval 510 minute), '%Y-%m-%d') mfgdate,
|
|
case when LOCATE('自产',material_name) >0 then mid(material_name,LOCATE('自产',material_name)+3,6)
|
|
when LOCATE('代工',material_name) >0 then mid(material_name,LOCATE('代工',material_name)+3,6) end subprod
|
|
from mes_sync.mes_square_task
|
|
where 1 = 1
|
|
and status = '3';
|
|
##滚磨
|
|
replace into cutsquarestepyield(step, crystalbarno, eqpid, createtime, mfgdate)
|
|
select 'roll' step,
|
|
square_code,
|
|
roll_machine,
|
|
update_time,
|
|
date_format(date_sub(update_time, interval 510 minute), '%Y-%m-%d') mfgdate
|
|
from mes_sync.mes_roll_rub_task;
|
|
|
|
###更新有效长度
|
|
UPDATE reportdata.cutsquarestepyield cq
|
|
INNER JOIN (SELECT production_code,
|
|
sum(draw_length) qclength
|
|
FROM mes_sync.mes_round_bar
|
|
where right(code, 1) = '0'
|
|
group by production_code) ps on (cq.crystalbarno = ps.production_code)
|
|
set cq.length = ps.qclength
|
|
where 1 = 1
|
|
and step = 'cut'
|
|
and cq.length is null;
|
|
|
|
|
|
UPDATE reportdata.cutsquarestepyield cq
|
|
inner join mes_sync.mes_round_bar mrb on (cq.crystalbarno = mrb.code)
|
|
set cq.length = mrb.actual_length
|
|
where 1 = 1
|
|
and step = 'square' or step = 'roll'
|
|
and cq.length is null
|
|
or cq.weight is null;
|
|
|
|
UPDATE reportdata.cutsquarestepyield cq
|
|
inner join
|
|
(select crystalbarno, subprod
|
|
from cutsquarestepyield
|
|
where step = 'square'
|
|
)t on t.crystalbarno = cq.crystalbarno and cq.step = 'roll'
|
|
set cq.subprod = t.subprod
|
|
where cq.subprod is null;
|
|
|
|
select *
|
|
from cutsquarestepyield; |