CREATE DEFINER=root
@%
PROCEDURE mjcio
(in fg int,in mjcidt bigint(15),out error int)
lj:BEGIN
declare clorderby varchar(1);
declare stylest varchar(20);
declare lbt varchar(4);
declare bomidt BIGINT(15);
declare xht int DEFAULT 0;
SET error=0;
/*
if ifnull(fg,0)=1 and exists(select id from cgs where ifnull(stat,‘’)=‘已开’ and FIND_IN_SET(id,cgsidt)>=1) then
set error=9; – 9 ‘已开’ 物料需求记录不能重复转采购开单!
select error;
leave lj;
end if; */
if ifnull(fg,0)=1 then
select styles,lb into stylest,lbt from mjc where id=mjcidt;
select id into bomidt from bom where styles=stylest;
select orderby into clorderby from bomsz where bomid=bomidt ORDER BY orderby LIMIT 0,1 ;
drop TEMPORARY TABLE if EXISTS mjcs_boms;
CREATE TEMPORARY TABLE if not EXISTS mjcs_boms(SELECT bomid,id,bwmc,bomszid FROM boms WHERE bomid=bomidt and FIND_IN_SET(clorderby,bomszid)>0);
DROP TEMPORARY TABLE IF EXISTS mjcs_boms2;
CREATE TEMPORARY TABLE if not EXISTS mjcs_boms2(SELECT DISTINCT substring_index(substring_index(a.bwmc,‘+’,b.help_topic_id+1),‘+’,- 1) AS bwmc FROM mjcs_boms a
INNER JOIN mysql.help_topic b ON b.help_topic_id < (length(a.bwmc)-length(REPLACE(a.bwmc, ‘+’, ‘’)) + 1) );
delete from mjcs where mjcid=mjcidt;
set @xht=0;
insert into mjcs(mjcid,orderby,styles,bwmc,lb,wldw) select mjcidt,(@xht:=@xht+1),stylest,bwmc,lbt,CASE WHEN lbt=‘斩刀’ THEN ‘只’ WHEN lbt=‘面板’ or lbt=‘底板’ THEN ‘片’ ELSE ‘块’ end as wldw from mjcs_boms2;
DROP TEMPORARY TABLE IF EXISTS mjcs_boms;
DROP TEMPORARY TABLE IF EXISTS mjcs_boms2;
end if;
select error;
END