USE [MES]
GO

/****** Object:  StoredProcedure [dbo].[AMResultProc]    Script Date: 12/14/2021 16:10:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AMResultProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AMResultProc]
GO

USE [MES]
GO

/****** Object:  StoredProcedure [dbo].[AMResultProc]    Script Date: 12/14/2021 16:10:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AMResultProc](
	@Line varchar(30),			--线体
	@Station varchar(30),		--工站
	@TestDate DATE,				--日期
	@TestHour INT,				--整点
	@TestTime DATETIME,			--时间
	@SN varchar(30),			--SN
	@DSN varchar(30),			--DSN
	@ODF varchar(30),			--GetMaInfo.ODF
	@Model varchar(30),			--GetMaInfo.Model
	@Dimension varchar(30),		--GetMaInfo.SIZE
	@LastSN INT,				--=0,表示上一次抄写的SN与当前SN不相同，=1表示相同
	@Result INT,				--=0,表示抄写失败,=1表示抄写成功
	@ErrMsg varchar(300),		--出错描述;
	@Remark varchar(100)		--备注;
	)
AS BEGIN
	DECLARE @Count INT
	DECLARE @RedoCount INT
	DECLARE @Test01 varchar(30)
	DECLARE @Test02 varchar(30)
	DECLARE @FinalTest varchar(30)
	DECLARE @ResultType varchar(30)
	-- 查询是否有记录;
	SELECT @Count=COUNT(1) FROM AMResult WHERE SN=@SN and Station=@Station 
	--根据抄写结果：新增记录时的默认设置;
	IF @Result = 1	--抄写成功;
		BEGIN
			SET @Test01='PASS'
			SET @Test02='NULL'
			SET @FinalTest='PASS'
			SET @ResultType='OncePass'
		END
	ELSE			--抄写失败;
		BEGIN
			SET @Test01='FAIL'
			SET @Test02='NULL'
			SET @FinalTest='FAIL'
			SET @ResultType='OnceFail'
		END
	
	--无记录则添加,有则更新处理;
	IF @Count = 0
		BEGIN
			PRINT '无记录，插入新记录'
			--插入新记录;
			INSERT INTO [AMResult]([TestDate], [TestHour], [Line], [Station], [Model], [Dimension], [TestTime], [ODF], [SN], 
				[FinalTest], [ResultType], [ErrorMsg], [ReDo], [Remark], [Test01], [Test02], [DSN]) 
			VALUES (@TestDate, @TestHour, @Line, @Station, @Model, @Dimension, @TestTime, @ODF, @SN, @FinalTest, @ResultType, @ErrMsg, '0', @Remark, @Test01, @Test02, @DSN);
		END
	ELSE
		BEGIN
			IF @LastSN = 0	--非连续抄写,新增记录;
				BEGIN
					PRINT '有记录，非连续抄写,新增记录'
					--查询最大ReDo值;
					SELECT @RedoCount=MAX(ReDo) FROM AMResult WHERE SN=@SN and Station=@Station 
					--更新当前Redo=0的记录为Redo=MAX(Redo)+1;
					UPDATE AMResult SET ReDo=convert(varchar(30),@RedoCount+1) WHERE SN=@SN and Station=@Station and ReDo='0'
					--然后新增记录;
					INSERT INTO [AMResult]([TestDate], [TestHour], [Line], [Station], [Model], [Dimension], [TestTime], [ODF], [SN], 
						[FinalTest], [ResultType], [ErrorMsg], [ReDo], [Remark], [Test01], [Test02], [DSN]) 
					VALUES (@TestDate, @TestHour, @Line, @Station, @Model, @Dimension, @TestTime, @ODF, @SN, @FinalTest, @ResultType, @ErrMsg, '0', @Remark, @Test01, @Test02, @DSN);
				END
			ELSE			--连续抄写,更新记录;
				BEGIN
					PRINT '有记录，连续抄写,更新记录'
					DECLARE @ReDo0Test01 varchar(30)
					DECLARE @ReDo0Test02 varchar(30)
					DECLARE @ReDo0FinalTest varchar(30)
					DECLARE @ReDo0ResultType varchar(30)
					--查询Redo=0的状态;
					SELECT @ReDo0Test01=lower(Test01), @ReDo0Test02=lower(isnull(Test02,'null')), @ReDo0FinalTest=lower(FinalTest), @ReDo0ResultType=lower(ResultType) FROM AMResult WHERE SN=@SN and Station=@Station and ReDo='0'
					--原状态为：OncePass
					IF @ReDo0Test01 = 'pass' AND @ReDo0Test02 = 'null' AND @ReDo0FinalTest = 'pass'
						IF @Result = 1
							BEGIN
								SET @Test01=upper(@ReDo0Test01)
								SET @Test02='PASS'
								SET @FinalTest='PASS'
								SET @ResultType='FinalPass'
							END
						ELSE
							BEGIN
								SET @Test01=upper(@ReDo0Test01)
								SET @Test02='FAIL'
								SET @FinalTest='FAIL'
								SET @ResultType='FinalFail'
							END
					--原状态为：OnceFail
					ELSE IF @ReDo0Test01 = 'fail' AND @ReDo0Test02 = 'null' AND @ReDo0FinalTest = 'fail'
						IF @Result = 1
							BEGIN
								SET @Test01=upper(@ReDo0Test01)
								SET @Test02='PASS'
								SET @FinalTest='PASS'
								SET @ResultType='NTF'
							END
						ELSE
							BEGIN
								SET @Test01=upper(@ReDo0Test01)
								SET @Test02='FAIL'
								SET @FinalTest='FAIL'
								SET @ResultType='TwiceFail'
							END
					--原状态为：NTF 或 FinalFail
					ELSE IF @ReDo0Test01 = 'fail' AND @ReDo0Test02 = 'pass' AND (@ReDo0FinalTest = 'pass' OR @ReDo0FinalTest = 'fail')
						IF @Result = 1
							BEGIN
								SET @Test01=upper(@ReDo0Test01)
								SET @Test02=upper(@ReDo0Test02)
								SET @FinalTest='PASS'
								SET @ResultType='NTF'
							END
						ELSE
							BEGIN
								SET @Test01=upper(@ReDo0Test01)
								SET @Test02=upper(@ReDo0Test02)
								SET @FinalTest='FAIL'
								SET @ResultType='FinalFail'
							END
					ELSE	--原状态为：剩余的FinalPass 或 FinalFail
						IF @Result = 1
							BEGIN
								SET @Test01=upper(@ReDo0Test01)
								SET @Test02=upper(@ReDo0Test02)
								SET @FinalTest='PASS'
								SET @ResultType='FinalPass'
							END
						ELSE
							BEGIN
								SET @Test01=upper(@ReDo0Test01)
								SET @Test02=upper(@ReDo0Test02)
								SET @FinalTest='FAIL'
								SET @ResultType='FinalFail'
							END
					
					PRINT '当前抄写(0=失败,1=成功)：'+convert(varchar(30),@Result)+' 是否重流(0=重流,1=非重流)：'+convert(varchar(30),@LastSN)
					PRINT '现在状态（Test01\Test02\FinalTest\ResultType）：'+@Test01+'\'+@Test02+'\'+@FinalTest+'\'+@ResultType
					PRINT '之前状态（Test01\Test02\FinalTest\ResultType）：'+@ReDo0Test01+'\'+@ReDo0Test02+'\'+@ReDo0FinalTest+'\'+@ReDo0ResultType
					--更新Redo=0的状态;
					UPDATE AMResult SET Test01=@Test01, Test02=@Test02, FinalTest=@FinalTest, ResultType=@ResultType, ErrorMsg=@ErrMsg, Remark=@Remark WHERE SN=@SN and Station=@Station and ReDo='0'
				END
		END
	--默认返回;
END



GO