將以下SP整體邏輯取出，改為ORM的EF框架實現
--機台通知MES載具上的Component已進/出
ALTER PROC [dbo].[sp_MSMQ_CompPicked]
@TID VARCHAR(255),
@machineno VARCHAR(20), --子機台ID
@port_id VARCHAR(20)='',
@carrier_id VARCHAR(20)='',
@pick CHAR(1), --Component進/出載具 I:載具放入Component O:從載具取出Component
@Slot VARCHAR(10)='', --不指定Slot可傳空值
@SubCarID VARCHAR(30)='',
@CompID VARCHAR(50), --ComponentID，若為unit時則lot_id必填
@qnty INT,
@lot_id VARCHAR(50)='' --此comp的批號 (不一定正確，因為一載具多批pick in時，EAP無法分辨批號)
AS
DECLARE @result INT=999, @msg VARCHAR(255)='', @mMachineID VARCHAR(25), @existsCar BIT=0
, @compLotnum VARCHAR(20), @compIsProcEnd BIT, @carLotnum VARCHAR(20), @existsCompID VARCHAR(50), @IsFixedNumOfSlots BIT, @DefaultNumOfSlots INT
--紀錄執行時間
declare @processlog table
(
TID varchar(255),
KeyInfo varchar(50),
state varchar(200),
enttime datetime
)
insert into @processlog
select TID = @TID
,KeyInfo=concat('CarrierId:',@carrier_id,',','port:',@port_id)
,State='start'
,enttime=getdate()
IF @carrier_id='Unknown' OR LEFT(@carrier_id,7)='MANUALC' --如果載具ID讀不到或是手動維護的流水號就不用往下執行了
BEGIN
SELECT @result=0 --讀不到載具ID不要報錯誤
SELECT @msg='Carrier ID is Unknown, MES do nothing.'
GOTO final
END
--SET @CompID=[dbo].[fn_Trans_CompID](@CompID) --機台報上來的CompID要先做轉換，轉成table在用的CompID
--PRINT @CompID
SELECT @existsCar=1, @carLotnum=c.LotNum, @IsFixedNumOfSlots=t.IsFixedNumOfSlots, @DefaultNumOfSlots=t.DefaultNumOfSlots
FROM CAR_Carrier c (NOLOCK)
LEFT JOIN CAR_Type t (NOLOCK) ON c.Type=t.Type AND c.OwnerLineID=t.OwnerLineID
WHERE c.CarrierID = @carrier_id
IF @existsCar=0
BEGIN
SELECT @result=16
SELECT @msg=CONCAT('[Error_MES_', @result
,'] CarrierID:', @carrier_id, ' not found!!')
GOTO final
END
SELECT @mMachineID=m.MachineId
FROM PDL_MachineDtl d (NOLOCK)
LEFT JOIN PDL_Machine m (NOLOCK) ON d.MachineNo=m.MachineNo
WHERE d.Id=@machineno
IF @mMachineID IS NULL
BEGIN
SELECT @result=2
SELECT @msg=CONCAT('[Error_MES_', @result
,'] Sub EQ ID:', @machineno, ' not found correspond Main EQ!!')
GOTO final
END
-- 泰國廠,解綁時無需卡控compId,只需傳入trayId,但是綁定時需要傳入stripId 和 trayId.
IF @pick='I'
BEGIN
IF @CompID='Unknown' --讀不到CompID就用EAP傳入的@lot_id
BEGIN
SELECT @compLotnum=@lot_id, @compIsProcEnd=0
END
ELSE --用CompID抓批號(不可直接用EAP傳入的@lot_id，因為一載具多批時EAP無法分辨批號)
BEGIN
SELECT @compLotnum=Lotnum, @compIsProcEnd=IsProcEnd
FROM CompInfo (NOLOCK) WHERE CompID=@CompID
IF dbo.fn_IsDummy(@CompID)=0 AND @compLotnum IS NULL --如果不是dummy則檢查是否有此Component
BEGIN
SELECT @result=15
SELECT @msg=CONCAT('[Error_MES_', @result
,'] CompID:', @CompID, ' is not Dummy and can not found in Component !!')
GOTO final
END
END
end
-- date: 2025/04/14 author: hayden 泰國廠工單信息 WIP2ST_RECORD_CURRENT
--IF NOT EXISTS(SELECT 1 FROM lotinfo l (NOLOCK)
-- INNER JOIN StkNWIP w (NOLOCK) ON l.LotNum=w.LotNum AND l.Layer=w.Layer
-- WHERE w.Qnty>0 AND l.LotNum=@compLotnum) --不在WIP上則不用放批號進CAR_CarrierSlot (可能是已報廢的dummy)
-- end =============================================================================================================
IF NOT EXISTS(SELECT 1 FROM [MES].[DBO].WIP2ST_RECORD_CURRENT (NOLOCK)
WHERE NUM_PICS>0 AND THIS_BASE_ID +THIS_LOT_ID = @lot_id) --不在WIP上則不用放批號進CAR_CarrierSlot (可能是已報廢的dummy)
BEGIN
SELECT @lot_id=NULL
END
--ELSE IF @lot_id='' OR @lot_id IS NULL --如果沒傳入@lot_id就用此Component的批號
-- OR @compLotnum IS NOT NULL --有抓到此Component的批號就用這個當批號
--BEGIN
-- SELECT @lot_id=@lot_id
--END
IF @pick='O' --取出Component
BEGIN
SELECT @existsCompID=CompID
FROM CAR_CarrierSlot(NOLOCK)
WHERE CarrierID=@carrier_id
AND SubCarID= @SubCarID --可只傳入Slot抽Comp，也可只傳入CompID抽Comp
--取出Component需檢查Carrier有無此Component
IF @existsCompID IS NULL
BEGIN
SELECT @result=17
SELECT @msg=CONCAT('[Error_MES_', @result
,'] CarrierID:',@carrier_id,' Slot:',@Slot,' not found Component that can pick out!')
GOTO final
END
--ELSE IF @existsCompID<>@CompID AND @existsCompID<>'Unknown' AND @CompID<>'Unknown'
--BEGIN
-- SELECT @result=18
-- SELECT @msg=CONCAT('[Error_MES_', @result
-- ,'] CarrierID:',@carrier_id,' Slot:',@Slot,' CompID:',@existsCompID,' is not match with CompID:',@CompID,' !')
-- GOTO final
--END
BEGIN TRAN O
--如果這片被指定為首件則取出後就清掉首件Flag
IF EXISTS(SELECT 1 FROM CompInfo(NOLOCK) WHERE CompID=@existsCompID AND ProcComp&4=4) --如果這片是首件
BEGIN
UPDATE CompInfo
SET ProcComp=ProcComp-4 --清掉首件Flag
WHERE CompID=@existsCompID
END
--更新載具組成
UPDATE CAR_CarrierSlot
SET CompID=NULL, Qnty=NULL, Lotnum=NULL, IsProcEnd=NULL,SubCarID=null
WHERE CarrierID=@carrier_id
AND SubCarID= @SubCarID
-- 更新子載具的組成
UPDATE CAR_CarrierSlot
SET CompID=NULL, Qnty=NULL, Lotnum=NULL, IsProcEnd=NULL
WHERE CarrierID=@SubCarID
UPDATE CAR_Carrier
SET parentId=NULL,lotnum=NULL
WHERE CarrierID=@SubCarID
--如果抽完後沒有除了Dummy的component就解除載具和批號的綁定
IF (SELECT ISNULL(SUM(Qnty),0)
FROM CAR_CarrierSlot(NOLOCK)
WHERE CarrierID=@carrier_id
AND dbo.fn_IsDummy(CompID)=0) = 0
BEGIN
UPDATE CAR_Carrier
SET LotNum=NULL
WHERE CarrierID=@carrier_id
END
COMMIT TRANSACTION O
END
ELSE IF @pick='I' --放入Component
BEGIN
DECLARE @compExistsOtherCarrierID VARCHAR(20)
SELECT @compExistsOtherCarrierID=CarrierID
FROM CAR_CarrierSlot(NOLOCK) WHERE CarrierID<>@carrier_id AND CompID=@CompID AND @CompID<>'Unknown'
/* 20210721 Harry 不卡已存在其他載具了，以最新上報的為準 (可能上次該被取出但沒讀到ID就沒取出)
if @compExistsCarrierID is not null
begin
select @result=19
select @msg=concat('[Error_MES_', @result
,'] CompID:',@CompID,' is already in CarrierID:',@compExistsCarrierID,' !!')
goto final
end*/
/* 20211124 Harry Pick In進載具不檢查有沒有同批了(MES只是忠實紀錄上報上來的資料)
if @lot_id<>@carLotnum --如果要放入的批號和載具上的已有批號不同則檢查是否符合一載具多批的條件
begin
create table #sp_CMS_PickInMultiLot_Check(
result int,
msg varchar(255)
)
insert into #sp_CMS_PickInMultiLot_Check(result, msg)
exec sp_CMS_PickInMultiLot_Check @carrier_id,@carLotnum,@lot_id
if not exists(select 1 from #sp_CMS_PickInMultiLot_Check where result=0) --不符合一載具多批的條件就不可放入不同批號的component
begin
select @result=28
select @msg=concat('[Error_MES_', @result
,'] CarrierID:',@carrier_id,' Carrier LotID:',@carLotnum,' is not match with CompID:',@CompID,' Comp LotID:',@lot_id,' !! '
,'(',(select top 1 msg from #sp_CMS_PickInMultiLot_Check),')')
goto final
end
end*/
IF NOT EXISTS(SELECT 1
FROM CAR_CarrierSlot (NOLOCK)
WHERE CarrierID=@carrier_id
AND Slot=@Slot)
AND @Slot<>'' --沒指定Slot就不用檢查
AND @IsFixedNumOfSlots=1 --固定Slot數的載具才要檢查
BEGIN
SELECT @result=20
SELECT @msg=CONCAT('[Error_MES_', @result
,'] CarrierID:',@carrier_id,' not found Slot:',@Slot,' !!')
GOTO final
END
IF @Slot='' --如果沒指定Slot做放入載具，則取Slot最小的空位
BEGIN
--沒指定Slot又已有此CompID在載具內，忽略 (可能是EAP上報重複Tx)
IF EXISTS(SELECT 1 FROM CAR_CarrierSlot(NOLOCK) WHERE CarrierID=@carrier_id AND CompID=@CompID)
AND @CompID<>'Unknown' --讀不到不用檢查
BEGIN
SELECT @result=0, @msg=CONCAT(@CompID,' is already Exists in ',@carrier_id)
GOTO final
END
SELECT @Slot=CONVERT(VARCHAR(10),MIN(TRY_PARSE(Slot AS INT))) --取Slot最小的空位
FROM CAR_CarrierSlot --不要下nolock，怕取到相同
WHERE CarrierID = @carrier_id
AND SubCarID IS NULL AND CompID IS NULL
IF @Slot IS NULL
BEGIN
SELECT @result=20
SELECT @msg=CONCAT('[Error_MES_', @result
,'] CarrierID:',@carrier_id,' not found Empty Slot !!')
GOTO final
END
IF TRY_PARSE(@Slot AS INT)>@DefaultNumOfSlots AND @IsFixedNumOfSlots=1
BEGIN
SELECT @result=20
SELECT @msg=CONCAT('[Error_MES_', @result
,'] CarrierID:',@carrier_id,' not found Empty Slot !! (Fixed Number Of Slots and Slot:',@Slot,' is bigger than Default Num Of Slots:',@DefaultNumOfSlots)
GOTO final
END
END
ELSE --有指定Slot，檢查此Slot是否已放Component
BEGIN
SELECT @existsCompID=CompID
FROM CAR_CarrierSlot(NOLOCK)
WHERE CarrierID=@carrier_id
AND Slot=@Slot
/* 20211217 Harry 不卡是否已有Component
if @existsCompID is not null
begin
select @result=21
select @msg=concat('[Error_MES_', @result
,'] CarrierID:',@carrier_id,' Slot:',@Slot,' has exists CompID:',@existsCompID,' !!')
goto final
end
*/
END
BEGIN TRAN I
--更新Slot中的Component--begin--
IF @IsFixedNumOfSlots=0
AND NOT EXISTS(SELECT 1 FROM CAR_CarrierSlot (NOLOCK)
WHERE CarrierID=@carrier_id AND Slot=@Slot) --如果slot數不固定而且沒有此Slot則insert此slot
BEGIN
INSERT INTO CAR_CarrierSlot(CarrierID,Slot,SubCarID,CompID,Qnty,Lotnum,IsProcEnd)
SELECT @carrier_id, @Slot, NULL, @CompID, @qnty, IIF(ISNULL(@lot_id,'')='',NULL,@lot_id), @compIsProcEnd
END
ELSE
BEGIN
UPDATE CAR_CarrierSlot
SET CompID=@CompID, Qnty=@qnty, LotNum=IIF(ISNULL(@lot_id,'')='',NULL,@lot_id), IsProcEnd=@compIsProcEnd
WHERE CarrierID=@carrier_id AND Slot=@Slot
END
--更新Slot中的Component--end--
--以最新上報的為準，若這個Comp有在其他載具上則他在其他載具上的CompID改為Unknown (若是同個載具上就不用變，一載具上報多個同CompID也OK)--begin--
IF @compExistsOtherCarrierID IS NOT NULL
BEGIN
--更新其他載具組成
UPDATE CAR_CarrierSlot
SET CompID='Unknown'
WHERE CarrierID=@compExistsOtherCarrierID AND CompID=@CompID
END
--以最新上報的為準--end--
--如果載具還沒綁定批號而且放進來的不是Dummy就綁定批號 (生產Dummy時也可能有Dummy批號，需綁定)
IF @carLotnum IS NULL AND dbo.fn_IsDummy(@CompID)=0 AND @lot_id IS NOT NULL
BEGIN
UPDATE CAR_Carrier
SET LotNum=@lot_id
WHERE CarrierID=@carrier_id
END
COMMIT TRANSACTION I
END
ELSE
BEGIN
SELECT @result=98
SELECT @msg=CONCAT('[Error_MES_', @result
,'] pick:', @pick, ' must be I or O !! Please Contact AIOT')
GOTO final
END
select @result=0, @msg=concat('Slot:',@Slot,' lot_id:',@lot_id,' OK')
--寫LOG
--INSERT INTO MSMQ_CompPicked(TID,machineno,port_id,carrier_id,pick,Slot,SubCarID,CompID,qnty,lot_id,enttime,HostName)
--VALUES(@TID,@machineno,@port_id,@carrier_id,@pick,@Slot,@SubCarID,@CompID,@qnty,@lot_id,GETDATE(),HOST_NAME())
final:
--紀錄執行時間
insert into @processlog
select TID = @TID
,KeyInfo=concat('CarrierId:',@carrier_id,',','port:',@port_id)
,State='更新CAR_Carrier CheckInTime--End'
,enttime=getdate()
select @result as result, @msg as msg
--紀錄執行時間
insert into __Execution_Time_log
(SourceName,TID,KeyInfo,state,enttime)
select 'sp_MSMQ_CompPicked',TID,KeyInfo,state,enttime from @processlog
IF (OBJECT_ID('tempdb.dbo.#sp_CMS_PickInMultiLot_Check')) Is Not Null
Drop Table #sp_CMS_PickInMultiLot_Check;
最新发布
本文详细介绍了数据库操作中的事务处理过程，包括BeginTran、CommitTran、RollbackTran及SaveTran的使用方法。通过具体示例展示了如何利用这些命令确保数据的一致性和安全性。
扫一扫
3052
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
