存储过程

USE [TraceabilityDB]
GO
/****** Object:  Trigger [dbo].[T_COILINFO_RECORD]    Script Date: 02/06/2014 11:27:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------
開始終了報告テーブルINSERT時の処理
  ・開始終了テーブルをオーダーNo, コイルNoごとに展開してコイル実績情報表示テーブルを更新
  ・開始終了テーブルをオーダーNo, コイルNoごとに展開してコイル状態テーブルを更新
--------------------------------------------------*/
ALTER TRIGGER [dbo].[T_COILINFO_RECORD] ON [dbo].[T_STARTENDDATA] AFTER INSERT
AS

SET NOCOUNT ON

DECLARE @i				INT				-- ループカウンタ
DECLARE @coilCount		INT				-- コイル数

DECLARE @message		NVARCHAR(MAX)	-- エラーメッセージバッファ

DECLARE @orderNo		NVARCHAR(10)	-- オーダーNoバッファ
DECLARE @coilNo		NVARCHAR(4)			-- コイルNoバッファ
DECLARE @serialNo		NVARCHAR(14)	-- シリアルNoバッファ
DECLARE @count			INT				-- 既存レコード件数バッファ
DECLARE @startTime		NVARCHAR(16)	-- 開始日時バッファ
DECLARE @endTime		NVARCHAR(16)	-- 終了日時バッファ


--T_STARTENDDATA fields
DECLARE @ID int
DECLARE @SEQUENCENO bigint
DECLARE @EQUIPMENTCODE nvarchar(4)
DECLARE @EQUIPMENTDETAILCODE1 nvarchar(4)
DECLARE @EQUIPMENTDETAILCODE2 nvarchar(4)
DECLARE @SENDYEAR int
DECLARE @SENDMONTH int
DECLARE @SENDDAY int
DECLARE @SENDHOUR int
DECLARE @SENDMINUTE int
DECLARE @STARTENDFLG int
DECLARE @ORDERNO1 nvarchar(10)
DECLARE @COILNO1 nvarchar(4)
DECLARE @ORDERNO2 nvarchar(10)
DECLARE @COILNO2 nvarchar(4)
DECLARE @ORDERNO3 nvarchar(10)
DECLARE @COILNO3 nvarchar(4)
DECLARE @ORDERNO4 nvarchar(10)
DECLARE @COILNO4 nvarchar(4)
DECLARE @ORDERNO5 nvarchar(10)
DECLARE @COILNO5 nvarchar(4)
DECLARE @ORDERNO6 nvarchar(10)
DECLARE @COILNO6 nvarchar(4)
DECLARE @ORDERNO7 nvarchar(10)
DECLARE @COILNO7 nvarchar(4)
DECLARE @ORDERNO8 nvarchar(10)
DECLARE @COILNO8 nvarchar(4)
DECLARE @ORDERNO9 nvarchar(10)
DECLARE @COILNO9 nvarchar(4)
DECLARE @ORDERNO10 nvarchar(10)
DECLARE @COILNO10 nvarchar(4)
DECLARE @ORDERNO11 nvarchar(10)
DECLARE @COILNO11 nvarchar(4)
DECLARE @ORDERNO12 nvarchar(10)
DECLARE @COILNO12 nvarchar(4)
DECLARE @ORDERNO13 nvarchar(10)
DECLARE @COILNO13 nvarchar(4)
DECLARE @ORDERNO14 nvarchar(10)
DECLARE @COILNO14 nvarchar(4)
DECLARE @ORDERNO15 nvarchar(10)
DECLARE @COILNO15 nvarchar(4)
DECLARE @ORDERNO16 nvarchar(10)
DECLARE @COILNO16 nvarchar(4)
DECLARE @ORDERNO17 nvarchar(10)
DECLARE @COILNO17 nvarchar(4)
DECLARE @ORDERNO18 nvarchar(10)
DECLARE @COILNO18 nvarchar(4)
DECLARE @ORDERNO19 nvarchar(10)
DECLARE @COILNO19 nvarchar(4)
DECLARE @ORDERNO20 nvarchar(10)
DECLARE @COILNO20 nvarchar(4)
DECLARE @ORDERNO21 nvarchar(10)
DECLARE @COILNO21 nvarchar(4)
DECLARE @ORDERNO22 nvarchar(10)
DECLARE @COILNO22 nvarchar(4)
DECLARE @ORDERNO23 nvarchar(10)
DECLARE @COILNO23 nvarchar(4)
DECLARE @ORDERNO24 nvarchar(10)
DECLARE @COILNO24 nvarchar(4)
DECLARE @ORDERNO25 nvarchar(10)
DECLARE @COILNO25 nvarchar(4)
DECLARE @ORDERNO26 nvarchar(10)
DECLARE @COILNO26 nvarchar(4)
DECLARE @ORDERNO27 nvarchar(10)
DECLARE @COILNO27 nvarchar(4)
DECLARE @ORDERNO28 nvarchar(10)
DECLARE @COILNO28 nvarchar(4)
DECLARE @ORDERNO29 nvarchar(10)
DECLARE @COILNO29 nvarchar(4)
DECLARE @ORDERNO30 nvarchar(10)
DECLARE @COILNO30 nvarchar(4)
DECLARE @ORDERNO31 nvarchar(10)
DECLARE @COILNO31 nvarchar(4)
DECLARE @ORDERNO32 nvarchar(10)
DECLARE @COILNO32 nvarchar(4)
DECLARE @ORDERNO33 nvarchar(10)
DECLARE @COILNO33 nvarchar(4)
DECLARE @ORDERNO34 nvarchar(10)
DECLARE @COILNO34 nvarchar(4)
DECLARE @ORDERNO35 nvarchar(10)
DECLARE @COILNO35 nvarchar(4)
DECLARE @ORDERNO36 nvarchar(10)
DECLARE @COILNO36 nvarchar(4)
DECLARE @ORDERNO37 nvarchar(10)
DECLARE @COILNO37 nvarchar(4)
DECLARE @ORDERNO38 nvarchar(10)
DECLARE @COILNO38 nvarchar(4)
DECLARE @ORDERNO39 nvarchar(10)
DECLARE @COILNO39 nvarchar(4)
DECLARE @ORDERNO40 nvarchar(10)
DECLARE @COILNO40 nvarchar(4)
DECLARE @ORDERNO41 nvarchar(10)
DECLARE @COILNO41 nvarchar(4)
DECLARE @ORDERNO42 nvarchar(10)
DECLARE @COILNO42 nvarchar(4)
DECLARE @ORDERNO43 nvarchar(10)
DECLARE @COILNO43 nvarchar(4)
DECLARE @ORDERNO44 nvarchar(10)
DECLARE @COILNO44 nvarchar(4)
DECLARE @ORDERNO45 nvarchar(10)
DECLARE @COILNO45 nvarchar(4)
DECLARE @ORDERNO46 nvarchar(10)
DECLARE @COILNO46 nvarchar(4)
DECLARE @ORDERNO47 nvarchar(10)
DECLARE @COILNO47 nvarchar(4)
DECLARE @ORDERNO48 nvarchar(10)
DECLARE @COILNO48 nvarchar(4)
DECLARE @ORDERNO49 nvarchar(10)
DECLARE @COILNO49 nvarchar(4)
DECLARE @ORDERNO50 nvarchar(10)
DECLARE @COILNO50 nvarchar(4)
DECLARE @ORDERNO51 nvarchar(10)
DECLARE @COILNO51 nvarchar(4)
DECLARE @ORDERNO52 nvarchar(10)
DECLARE @COILNO52 nvarchar(4)
DECLARE @ORDERNO53 nvarchar(10)
DECLARE @COILNO53 nvarchar(4)
DECLARE @ORDERNO54 nvarchar(10)
DECLARE @COILNO54 nvarchar(4)
DECLARE @ORDERNO55 nvarchar(10)
DECLARE @COILNO55 nvarchar(4)
DECLARE @ORDERNO56 nvarchar(10)
DECLARE @COILNO56 nvarchar(4)
DECLARE @ORDERNO57 nvarchar(10)
DECLARE @COILNO57 nvarchar(4)
DECLARE @ORDERNO58 nvarchar(10)
DECLARE @COILNO58 nvarchar(4)
DECLARE @ORDERNO59 nvarchar(10)
DECLARE @COILNO59 nvarchar(4)
DECLARE @ORDERNO60 nvarchar(10)
DECLARE @COILNO60 nvarchar(4)
DECLARE @PROCESSCOUNT int
DECLARE @PROCESSSTEP nvarchar(20)
DECLARE @BARCODEFLG int
DECLARE @WORKERCODE1 nvarchar(10)
DECLARE @WORKERCODE2 nvarchar(10)
DECLARE @WORKERCODE3 nvarchar(10)
DECLARE @WORKERCODE4 nvarchar(10)
DECLARE @WORKERCODE5 nvarchar(10)
DECLARE @ITEMNO1 int
DECLARE @ITEMNO2 int
DECLARE @ITEMNO3 int
DECLARE @ITEMNO4 int
DECLARE @ITEMNO5 int
DECLARE @ITEMNO6 int
DECLARE @ITEMNO7 int
DECLARE @ITEMNO8 int
DECLARE @ITEMNO9 int
DECLARE @ITEMNO10 int
DECLARE @ITEMNO11 int
DECLARE @ITEMNO12 int
DECLARE @ITEMNO13 int
DECLARE @ITEMNO14 int
DECLARE @ITEMNO15 int
DECLARE @ITEMNO16 int
DECLARE @ITEMNO17 int
DECLARE @ITEMNO18 int
DECLARE @ITEMNO19 int
DECLARE @ITEMNO20 int
DECLARE @VALUE1 int
DECLARE @VALUE2 int
DECLARE @VALUE3 int
DECLARE @VALUE4 int
DECLARE @VALUE5 int
DECLARE @VALUE6 int
DECLARE @VALUE7 int
DECLARE @VALUE8 int
DECLARE @VALUE9 int
DECLARE @VALUE10 int
DECLARE @VALUE11 nvarchar(10)
DECLARE @VALUE12 nvarchar(10)
DECLARE @VALUE13 nvarchar(10)
DECLARE @VALUE14 nvarchar(10)
DECLARE @VALUE15 nvarchar(10)
DECLARE @VALUE16 nvarchar(10)
DECLARE @VALUE17 nvarchar(10)
DECLARE @VALUE18 nvarchar(10)
DECLARE @VALUE19 nvarchar(10)
DECLARE @VALUE20 nvarchar(10)

/*最新ID取得*/
SET @ID = (SELECT MAX(ID) FROM T_STARTENDDATA)

/*レコード取得*/
DECLARE StartEndCur CURSOR FOR
SELECT
	EQUIPMENTCODE, EQUIPMENTDETAILCODE1, EQUIPMENTDETAILCODE2
	, SENDYEAR, SENDMONTH, SENDDAY, SENDHOUR, SENDMINUTE
	, STARTENDFLG
	, ORDERNO1, COILNO1, ORDERNO2, COILNO2, ORDERNO3, COILNO3, ORDERNO4, COILNO4, ORDERNO5, COILNO5, ORDERNO6, COILNO6, ORDERNO7, COILNO7, ORDERNO8, COILNO8, ORDERNO9, COILNO9, ORDERNO10, COILNO10
	, ORDERNO11, COILNO11, ORDERNO12, COILNO12, ORDERNO13, COILNO13, ORDERNO14, COILNO14, ORDERNO15, COILNO15, ORDERNO16, COILNO16, ORDERNO17, COILNO17, ORDERNO18, COILNO18, ORDERNO19, COILNO19, ORDERNO20, COILNO20
	, ORDERNO21, COILNO21, ORDERNO22, COILNO22, ORDERNO23, COILNO23, ORDERNO24, COILNO24, ORDERNO25, COILNO25, ORDERNO26, COILNO26, ORDERNO27, COILNO27, ORDERNO28, COILNO28, ORDERNO29, COILNO29, ORDERNO30, COILNO30
	, ORDERNO31, COILNO31, ORDERNO32, COILNO32, ORDERNO33, COILNO33, ORDERNO34, COILNO34, ORDERNO35, COILNO35, ORDERNO36, COILNO36, ORDERNO37, COILNO37, ORDERNO38, COILNO38, ORDERNO39, COILNO39, ORDERNO40, COILNO40
	, ORDERNO41, COILNO41, ORDERNO42, COILNO42, ORDERNO43, COILNO43, ORDERNO44, COILNO44, ORDERNO45, COILNO45, ORDERNO46, COILNO46, ORDERNO47, COILNO47, ORDERNO48, COILNO48, ORDERNO49, COILNO49, ORDERNO50, COILNO50
	, ORDERNO51, COILNO51, ORDERNO52, COILNO52, ORDERNO53, COILNO53, ORDERNO54, COILNO54, ORDERNO55, COILNO55, ORDERNO56, COILNO56, ORDERNO57, COILNO57, ORDERNO58, COILNO58, ORDERNO59, COILNO59, ORDERNO60, COILNO60
	, PROCESSCOUNT, PROCESSSTEP, BARCODEFLG
	, WORKERCODE1, WORKERCODE2, WORKERCODE3, WORKERCODE4, WORKERCODE5
	, ITEMNO1, VALUE1, ITEMNO2, VALUE2, ITEMNO3, VALUE3, ITEMNO4, VALUE4, ITEMNO5, VALUE5, ITEMNO6, VALUE6, ITEMNO7, VALUE7, ITEMNO8, VALUE8, ITEMNO9, VALUE9, ITEMNO10, VALUE10
	, ITEMNO11, VALUE11, ITEMNO12, VALUE12, ITEMNO13, VALUE13, ITEMNO14, VALUE14, ITEMNO15, VALUE15, ITEMNO16, VALUE16, ITEMNO17, VALUE17, ITEMNO18, VALUE18, ITEMNO19, VALUE19, ITEMNO20, VALUE20
FROM
	T_STARTENDDATA
WHERE
	ID = @ID

BEGIN TRY

	BEGIN TRANSACTION

	OPEN StartEndCur
	FETCH NEXT FROM StartEndCur INTO
		@EQUIPMENTCODE, @EQUIPMENTDETAILCODE1, @EQUIPMENTDETAILCODE2
		, @SENDYEAR, @SENDMONTH, @SENDDAY, @SENDHOUR, @SENDMINUTE
		, @STARTENDFLG
		, @ORDERNO1, @COILNO1, @ORDERNO2, @COILNO2, @ORDERNO3, @COILNO3, @ORDERNO4, @COILNO4, @ORDERNO5, @COILNO5, @ORDERNO6, @COILNO6, @ORDERNO7, @COILNO7, @ORDERNO8, @COILNO8, @ORDERNO9, @COILNO9, @ORDERNO10, @COILNO10
		, @ORDERNO11, @COILNO11, @ORDERNO12, @COILNO12, @ORDERNO13, @COILNO13, @ORDERNO14, @COILNO14, @ORDERNO15, @COILNO15, @ORDERNO16, @COILNO16, @ORDERNO17, @COILNO17, @ORDERNO18, @COILNO18, @ORDERNO19, @COILNO19, @ORDERNO20, @COILNO20
		, @ORDERNO21, @COILNO21, @ORDERNO22, @COILNO22, @ORDERNO23, @COILNO23, @ORDERNO24, @COILNO24, @ORDERNO25, @COILNO25, @ORDERNO26, @COILNO26, @ORDERNO27, @COILNO27, @ORDERNO28, @COILNO28, @ORDERNO29, @COILNO29, @ORDERNO30, @COILNO30
		, @ORDERNO31, @COILNO31, @ORDERNO32, @COILNO32, @ORDERNO33, @COILNO33, @ORDERNO34, @COILNO34, @ORDERNO35, @COILNO35, @ORDERNO36, @COILNO36, @ORDERNO37, @COILNO37, @ORDERNO38, @COILNO38, @ORDERNO39, @COILNO39, @ORDERNO40, @COILNO40
		, @ORDERNO41, @COILNO41, @ORDERNO42, @COILNO42, @ORDERNO43, @COILNO43, @ORDERNO44, @COILNO44, @ORDERNO45, @COILNO45, @ORDERNO46, @COILNO46, @ORDERNO47, @COILNO47, @ORDERNO48, @COILNO48, @ORDERNO49, @COILNO49, @ORDERNO50, @COILNO50
		, @ORDERNO51, @COILNO51, @ORDERNO52, @COILNO52, @ORDERNO53, @COILNO53, @ORDERNO54, @COILNO54, @ORDERNO55, @COILNO55, @ORDERNO56, @COILNO56, @ORDERNO57, @COILNO57, @ORDERNO58, @COILNO58, @ORDERNO59, @COILNO59, @ORDERNO60, @COILNO60
		, @PROCESSCOUNT, @PROCESSSTEP, @BARCODEFLG
		, @WORKERCODE1, @WORKERCODE2, @WORKERCODE3, @WORKERCODE4, @WORKERCODE5
		, @ITEMNO1, @VALUE1, @ITEMNO2, @VALUE2, @ITEMNO3, @VALUE3, @ITEMNO4, @VALUE4, @ITEMNO5, @VALUE5, @ITEMNO6, @VALUE6, @ITEMNO7, @VALUE7, @ITEMNO8, @VALUE8, @ITEMNO9, @VALUE9, @ITEMNO10, @VALUE10
		, @ITEMNO11, @VALUE11, @ITEMNO12, @VALUE12, @ITEMNO13, @VALUE13, @ITEMNO14, @VALUE14, @ITEMNO15, @VALUE15, @ITEMNO16, @VALUE16, @ITEMNO17, @VALUE17, @ITEMNO18, @VALUE18, @ITEMNO19, @VALUE19, @ITEMNO20, @VALUE20

	--バーコードフラグ無のチェック
	IF (@BARCODEFLG = 0)
	BEGIN
		SET @message = 'バーコード無し'
		PRINT(@message)
		CLOSE StartEndCur
		DEALLOCATE StartEndCur
		COMMIT TRANSACTION
		RETURN
	END

	--開始/終了日時
	IF (@STARTENDFLG = 0)
	BEGIN
		SET @startTime = 
			CAST(RIGHT('0000' +	CAST(@SENDYEAR AS VARCHAR), 4) AS NVARCHAR) + '/' +
			CAST(RIGHT('00' +		CAST(@SENDMONTH AS VARCHAR), 2) AS NVARCHAR) + '/' +
			CAST(RIGHT('00' +		CAST(@SENDDAY AS VARCHAR), 2) AS NVARCHAR) + ' ' +
			CAST(RIGHT('00' +		CAST(@SENDHOUR AS VARCHAR), 2) AS NVARCHAR) + ':' +
			CAST(RIGHT('00' +		CAST(@SENDMINUTE AS VARCHAR), 2) AS NVARCHAR)
		SET @endTime = null
	END
	ELSE IF (@STARTENDFLG = 1)
	BEGIN
		SET @startTime = null
		SET @endTime = 
			CAST(RIGHT('0000' +	CAST(@SENDYEAR AS VARCHAR), 4) AS NVARCHAR) + '/' +
			CAST(RIGHT('00' +		CAST(@SENDMONTH AS VARCHAR), 2) AS NVARCHAR) + '/' +
			CAST(RIGHT('00' +		CAST(@SENDDAY AS VARCHAR), 2) AS NVARCHAR) + ' ' +
			CAST(RIGHT('00' +		CAST(@SENDHOUR AS VARCHAR), 2) AS NVARCHAR) + ':' +
			CAST(RIGHT('00' +		CAST(@SENDMINUTE AS VARCHAR), 2) AS NVARCHAR)
	END

	--コイル数分繰り返す
	SET @i = 0
	WHILE (@i <= @PROCESSCOUNT)
	BEGIN
  
		SET @orderNo = null
		SET @coilNo = null
		
		PRINT '----コイル数分 :' + CAST(@i AS nvarchar) + '-------PROCESSCOUNT :' +  CAST(@PROCESSCOUNT AS nvarchar)

		IF (@i = 1)
		BEGIN
			SET @orderNo = @ORDERNO1
			SET @coilNo = @COILNO1
		END
		ELSE IF (@i = 2)
		BEGIN
			SET @orderNo = @ORDERNO2
			SET @coilNo = @COILNO2
		END
		ELSE IF (@i = 3)
		BEGIN
			SET @orderNo = @ORDERNO3
			SET @coilNo = @COILNO3
		END
		ELSE IF (@i = 4)
		BEGIN
			SET @orderNo = @ORDERNO4
			SET @coilNo = @COILNO4
		END
		ELSE IF (@i = 5)
		BEGIN
			SET @orderNo = @ORDERNO5
			SET @coilNo = @COILNO5
		END
		ELSE IF (@i = 6)
		BEGIN
			SET @orderNo = @ORDERNO6
			SET @coilNo = @COILNO6
		END
		ELSE IF (@i = 7)
		BEGIN
			SET @orderNo = @ORDERNO7
			SET @coilNo = @COILNO7
		END
		ELSE IF (@i = 8)
		BEGIN
			SET @orderNo = @ORDERNO8
			SET @coilNo = @COILNO8
		END
		ELSE IF (@i = 9)
		BEGIN
			SET @orderNo = @ORDERNO9
			SET @coilNo = @COILNO9
		END
		ELSE IF (@i = 10)
		BEGIN
			SET @orderNo = @ORDERNO10
			SET @coilNo = @COILNO10
		END
		ELSE IF (@i = 11)
		BEGIN
			SET @orderNo = @ORDERNO11
			SET @coilNo = @COILNO11
		END
		ELSE IF (@i = 12)
		BEGIN
			SET @orderNo = @ORDERNO12
			SET @coilNo = @COILNO12
		END
		ELSE IF (@i = 13)
		BEGIN
			SET @orderNo = @ORDERNO13
			SET @coilNo = @COILNO13
		END
		ELSE IF (@i = 14)
		BEGIN
			SET @orderNo = @ORDERNO14
			SET @coilNo = @COILNO14
		END
		ELSE IF (@i = 15)
		BEGIN
			SET @orderNo = @ORDERNO15
			SET @coilNo = @COILNO15
		END
		ELSE IF (@i = 16)
		BEGIN
			SET @orderNo = @ORDERNO16
			SET @coilNo = @COILNO16
		END
		ELSE IF (@i = 17)
		BEGIN
			SET @orderNo = @ORDERNO17
			SET @coilNo = @COILNO17
		END
		ELSE IF (@i = 18)
		BEGIN
			SET @orderNo = @ORDERNO18
			SET @coilNo = @COILNO18
		END
		ELSE IF (@i = 19)
		BEGIN
			SET @orderNo = @ORDERNO19
			SET @coilNo = @COILNO19
		END
		ELSE IF (@i = 20)
		BEGIN
			SET @orderNo = @ORDERNO20
			SET @coilNo = @COILNO20
		END
		ELSE IF (@i = 21)
		BEGIN
			SET @orderNo = @ORDERNO21
			SET @coilNo = @COILNO21
		END
		ELSE IF (@i = 22)
		BEGIN
			SET @orderNo = @ORDERNO22
			SET @coilNo = @COILNO22
		END
		ELSE IF (@i = 23)
		BEGIN
			SET @orderNo = @ORDERNO23
			SET @coilNo = @COILNO23
		END
		ELSE IF (@i = 24)
		BEGIN
			SET @orderNo = @ORDERNO24
			SET @coilNo = @COILNO24
		END
		ELSE IF (@i = 25)
		BEGIN
			SET @orderNo = @ORDERNO25
			SET @coilNo = @COILNO25
		END
		ELSE IF (@i = 26)
		BEGIN
			SET @orderNo = @ORDERNO26
			SET @coilNo = @COILNO26
		END
		ELSE IF (@i = 27)
		BEGIN
			SET @orderNo = @ORDERNO27
			SET @coilNo = @COILNO27
		END
		ELSE IF (@i = 28)
		BEGIN
			SET @orderNo = @ORDERNO28
			SET @coilNo = @COILNO28
		END
		ELSE IF (@i = 29)
		BEGIN
			SET @orderNo = @ORDERNO29
			SET @coilNo = @COILNO29
		END
		ELSE IF (@i = 30)
		BEGIN
			SET @orderNo = @ORDERNO30
			SET @coilNo = @COILNO30
		END
		ELSE IF (@i = 31)
		BEGIN
			SET @orderNo = @ORDERNO31
			SET @coilNo = @COILNO31
		END
		ELSE IF (@i = 32)
		BEGIN
			SET @orderNo = @ORDERNO32
			SET @coilNo = @COILNO32
		END
		ELSE IF (@i = 33)
		BEGIN
			SET @orderNo = @ORDERNO33
			SET @coilNo = @COILNO33
		END
		ELSE IF (@i = 34)
		BEGIN
			SET @orderNo = @ORDERNO34
			SET @coilNo = @COILNO34
		END
		ELSE IF (@i = 35)
		BEGIN
			SET @orderNo = @ORDERNO35
			SET @coilNo = @COILNO35
		END
		ELSE IF (@i = 36)
		BEGIN
			SET @orderNo = @ORDERNO36
			SET @coilNo = @COILNO36
		END
		ELSE IF (@i = 37)
		BEGIN
			SET @orderNo = @ORDERNO37
			SET @coilNo = @COILNO37
		END
		ELSE IF (@i = 38)
		BEGIN
			SET @orderNo = @ORDERNO38
			SET @coilNo = @COILNO38
		END
		ELSE IF (@i = 39)
		BEGIN
			SET @orderNo = @ORDERNO39
			SET @coilNo = @COILNO39
		END
		ELSE IF (@i = 40)
		BEGIN
			SET @orderNo = @ORDERNO40
			SET @coilNo = @COILNO40
		END
		ELSE IF (@i = 41)
		BEGIN
			SET @orderNo = @ORDERNO41
			SET @coilNo = @COILNO41
		END
		ELSE IF (@i = 42)
		BEGIN
			SET @orderNo = @ORDERNO42
			SET @coilNo = @COILNO42
		END
		ELSE IF (@i = 43)
		BEGIN
			SET @orderNo = @ORDERNO43
			SET @coilNo = @COILNO43
		END
		ELSE IF (@i = 44)
		BEGIN
			SET @orderNo = @ORDERNO44
			SET @coilNo = @COILNO44
		END
		ELSE IF (@i = 45)
		BEGIN
			SET @orderNo = @ORDERNO45
			SET @coilNo = @COILNO45
		END
		ELSE IF (@i = 46)
		BEGIN
			SET @orderNo = @ORDERNO46
			SET @coilNo = @COILNO46
		END
		ELSE IF (@i = 47)
		BEGIN
			SET @orderNo = @ORDERNO47
			SET @coilNo = @COILNO47
		END
		ELSE IF (@i = 48)
		BEGIN
			SET @orderNo = @ORDERNO48
			SET @coilNo = @COILNO48
		END
		ELSE IF (@i = 49)
		BEGIN
			SET @orderNo = @ORDERNO49
			SET @coilNo = @COILNO49
		END
		ELSE IF (@i = 50)
		BEGIN
			SET @orderNo = @ORDERNO50
			SET @coilNo = @COILNO50
		END
		ELSE IF (@i = 51)
		BEGIN
			SET @orderNo = @ORDERNO51
			SET @coilNo = @COILNO51
		END
		ELSE IF (@i = 52)
		BEGIN
			SET @orderNo = @ORDERNO52
			SET @coilNo = @COILNO52
		END
		ELSE IF (@i = 53)
		BEGIN
			SET @orderNo = @ORDERNO53
			SET @coilNo = @COILNO53
		END
		ELSE IF (@i = 54)
		BEGIN
			SET @orderNo = @ORDERNO54
			SET @coilNo = @COILNO54
		END
		ELSE IF (@i = 55)
		BEGIN
			SET @orderNo = @ORDERNO55
			SET @coilNo = @COILNO55
		END
		ELSE IF (@i = 56)
		BEGIN
			SET @orderNo = @ORDERNO56
			SET @coilNo = @COILNO56
		END
		ELSE IF (@i = 57)
		BEGIN
			SET @orderNo = @ORDERNO57
			SET @coilNo = @COILNO57
		END
		ELSE IF (@i = 58)
		BEGIN
			SET @orderNo = @ORDERNO58
			SET @coilNo = @COILNO58
		END
		ELSE IF (@i = 59)
		BEGIN
			SET @orderNo = @ORDERNO59
			SET @coilNo = @COILNO59
		END
		ELSE IF (@i = 60)
		BEGIN
			SET @orderNo = @ORDERNO60
			SET @coilNo = @COILNO60
		END   
		
	    SET @i = @i + 1

		--ORDERNO, COILNO未入力時
		IF (@orderNo is null OR @coilNo is null)
		BEGIN
			SET @message = 'オーダーNoまたはコイルNoがNULL オーダーNo:' + @orderNo + ' コイルNo:' + @coilNo
			PRINT(@message)
			CONTINUE
		END

		--コイルテーブルをチェック
		SELECT @serialNo = SERIALCODE FROM T_COIL
		WHERE
			ORDERNO = @orderNo
			AND COILNO = @coilNo
			AND HISTORYFLG = 0
			AND (DELETEFLG IS NULL OR DELETEFLG = 0)

		IF @serialNo IS NULL
		BEGIN
			SET @message = 'T_COILに該当なし オーダーNo:' + @orderNo + ' コイルNo:' + @coilNo
			PRINT(@message)
			CONTINUE
		END

		--コイル実績情報表示テーブルの既存レコードを確認
		SELECT @count = COUNT(*) FROM T_COILINFO
		WHERE
			SERIALNO = @serialNo
			AND EQUIPMENTCODE = @EQUIPMENTCODE
		
		PRINT '-----コイル数分++ :' + CAST(@i AS nvarchar)-----'
		PRINT 'コイルカウント数:'+CAST(@coilCount AS nvarchar)
		PRINT 'コイル数分 :' + CAST(@i AS nvarchar)
		PRINT 'PROCESSCOUNT :' +  CAST(@PROCESSCOUNT AS nvarchar)
		PRINT 'ORDERNO  : ' + @orderNo
		PRINT 'COILNO   : ' + @coilNo
		PRINT 'EQCODE   : ' + @EQUIPMENTCODE
		PRINT 'EQDETAIL1: ' + @EQUIPMENTDETAILCODE1
		PRINT 'コイル実績情報表示テーブル(T_COILINFO)の既存レコードを確認'		
		PRINT 'SERIALNO :		'	+ @serialNo
		PRINT 'COUNT    :		' + CAST(@count AS nvarchar)

		--コイル実績情報表示テーブルに追加または更新
		IF @count = 0
		BEGIN
			--新規
			INSERT INTO T_COILINFO VALUES (
				@serialNo
				, @EQUIPMENTCODE
				, @EQUIPMENTDETAILCODE1
				, @EQUIPMENTDETAILCODE2
				, @startTime
				, @endTime
				, @WORKERCODE1
				, @WORKERCODE2
				, @WORKERCODE3
				, @WORKERCODE4
				, @WORKERCODE5
				, @ITEMNO1, @VALUE1
				, @ITEMNO2, @VALUE2
				, @ITEMNO3, @VALUE3
				, @ITEMNO4, @VALUE4
				, @ITEMNO5, @VALUE5
				, @ITEMNO6, @VALUE6
				, @ITEMNO7, @VALUE7
				, @ITEMNO8, @VALUE8
				, @ITEMNO9, @VALUE9
				, @ITEMNO10, @VALUE10
				, @ITEMNO11, @VALUE11
				, @ITEMNO12, @VALUE12
				, @ITEMNO13, @VALUE13
				, @ITEMNO14, @VALUE14
				, @ITEMNO15, @VALUE15
				, @ITEMNO16, @VALUE16
				, @ITEMNO17, @VALUE17
				, @ITEMNO18, @VALUE18
				, @ITEMNO19, @VALUE19
				, @ITEMNO20, @VALUE20
				, GETDATE()
				, GETDATE()
				, 'T_STARTENDDATA'
				, 0
			)
			  PRINT 'INSERT  T_COILINFO'
		END
		ELSE
		BEGIN
			--更新
			IF (@STARTENDFLG = 0)		/*開始*/
			BEGIN
				UPDATE T_COILINFO 
				SET
				  EQUIPMENTCODE = @EQUIPMENTCODE
				  , EQUIPMENTDETAILCODE1 = @EQUIPMENTDETAILCODE1
				  , EQUIPMENTDETAILCODE2 = @EQUIPMENTDETAILCODE2
				  , STARTTIME = @startTime
				  , ENDTIME = null
				  , WORKERCODE1 = @WORKERCODE1
				  , WORKERCODE2 = @WORKERCODE2
				  , WORKERCODE3 = @WORKERCODE3
				  , WORKERCODE4 = @WORKERCODE4
				  , WORKERCODE5 = @WORKERCODE5
				  , ITEMNO1 = @ITEMNO1, VALUE1 = @VALUE1
				  , ITEMNO2 = @ITEMNO2, VALUE2 = @VALUE2
				  , ITEMNO3 = @ITEMNO3, VALUE3 = @VALUE3
				  , ITEMNO4 = @ITEMNO4, VALUE4 = @VALUE4
				  , ITEMNO5 = @ITEMNO5, VALUE5 = @VALUE5
				  , ITEMNO6 = @ITEMNO6, VALUE6 = @VALUE6
				  , ITEMNO7 = @ITEMNO7, VALUE7 = @VALUE7
				  , ITEMNO8 = @ITEMNO8, VALUE8 = @VALUE8
				  , ITEMNO9 = @ITEMNO9, VALUE9 = @VALUE9
				  , ITEMNO10 = @ITEMNO10, VALUE10 = @VALUE10
				  , ITEMNO11 = @ITEMNO11, VALUE11 = @VALUE11
				  , ITEMNO12 = @ITEMNO12, VALUE12 = @VALUE12
				  , ITEMNO13 = @ITEMNO13, VALUE13 = @VALUE13
				  , ITEMNO14 = @ITEMNO14, VALUE14 = @VALUE14
				  , ITEMNO15 = @ITEMNO15, VALUE15 = @VALUE15
				  , ITEMNO16 = @ITEMNO16, VALUE16 = @VALUE16
				  , ITEMNO17 = @ITEMNO17, VALUE17 = @VALUE17
				  , ITEMNO18 = @ITEMNO18, VALUE18 = @VALUE18
				  , ITEMNO19 = @ITEMNO19, VALUE19 = @VALUE19
				  , ITEMNO20 = @ITEMNO20, VALUE20 = @VALUE20
				  , UPDATETIME = GETDATE()
				  , UPUSER = 'T_STARTENDDATA'
				  , DELETEFLG = 0
				WHERE
				  SERIALNO = @serialNo
				  AND EQUIPMENTCODE = @EQUIPMENTCODE
				  --PRINT 'UPDATE T_COILINFO SERIALNO='+@serialNo +' EQUIPMENTCODE=' + @EQUIPMENTCODE
				  --PRINT '		  EQUIPMENTCODE ='''+ @EQUIPMENTCODE+''
				  
				  
				  PRINT 'UPDATE T_COILINFO '
			END
			ELSE IF (@STARTENDFLG = 1)	/*終了*/
			print '@STARTENDFLG = '+ @STARTENDFLG
			BEGIN
				UPDATE T_COILINFO 
				SET
				  EQUIPMENTCODE = @EQUIPMENTCODE
				  , EQUIPMENTDETAILCODE1 = @EQUIPMENTDETAILCODE1
				  , EQUIPMENTDETAILCODE2 = @EQUIPMENTDETAILCODE2
				  , ENDTIME = @endTime
				  , WORKERCODE1 = @WORKERCODE1
				  , WORKERCODE2 = @WORKERCODE2
				  , WORKERCODE3 = @WORKERCODE3
				  , WORKERCODE4 = @WORKERCODE4
				  , WORKERCODE5 = @WORKERCODE5
				  , ITEMNO1 = @ITEMNO1, VALUE1 = @VALUE1
				  , ITEMNO2 = @ITEMNO2, VALUE2 = @VALUE2
				  , ITEMNO3 = @ITEMNO3, VALUE3 = @VALUE3
				  , ITEMNO4 = @ITEMNO4, VALUE4 = @VALUE4
				  , ITEMNO5 = @ITEMNO5, VALUE5 = @VALUE5
				  , ITEMNO6 = @ITEMNO6, VALUE6 = @VALUE6
				  , ITEMNO7 = @ITEMNO7, VALUE7 = @VALUE7
				  , ITEMNO8 = @ITEMNO8, VALUE8 = @VALUE8
				  , ITEMNO9 = @ITEMNO9, VALUE9 = @VALUE9
				  , ITEMNO10 = @ITEMNO10, VALUE10 = @VALUE10
				  , ITEMNO11 = @ITEMNO11, VALUE11 = @VALUE11
				  , ITEMNO12 = @ITEMNO12, VALUE12 = @VALUE12
				  , ITEMNO13 = @ITEMNO13, VALUE13 = @VALUE13
				  , ITEMNO14 = @ITEMNO14, VALUE14 = @VALUE14
				  , ITEMNO15 = @ITEMNO15, VALUE15 = @VALUE15
				  , ITEMNO16 = @ITEMNO16, VALUE16 = @VALUE16
				  , ITEMNO17 = @ITEMNO17, VALUE17 = @VALUE17
				  , ITEMNO18 = @ITEMNO18, VALUE18 = @VALUE18
				  , ITEMNO19 = @ITEMNO19, VALUE19 = @VALUE19
				  , ITEMNO20 = @ITEMNO20, VALUE20 = @VALUE20
				  , UPDATETIME = GETDATE()
				  , UPUSER = 'T_STARTENDDATA'
				  , DELETEFLG = 0
				WHERE
				  SERIALNO = @serialNo
				  AND EQUIPMENTCODE = @EQUIPMENTCODE 
			END
				  PRINT 'UPDATE T_COILINFO222222 '
		END
		
		--コイル状態テーブルの既存レコードを確認
		SELECT @count = COUNT(*) FROM T_COILTRACKING
		WHERE SERIALNO = @serialNo

		--コイル状態テーブルに追加または更新
		DECLARE @coilStatusId int
		IF (@STARTENDFLG = 1 AND @EQUIPMENTCODE = 'A050'/*仕上げ乾燥*/)
		BEGIN
			SET @coilStatusId = 6	/*仕上げ完了*/
		END
		ELSE
		BEGIN
			SET @coilStatusId = 2	/*投入中*/
		END

		IF @count = 0
		BEGIN
			--新規
			INSERT INTO T_COILTRACKING VALUES (
				@serialNo
				, @EQUIPMENTCODE
				, @EQUIPMENTDETAILCODE1
				, @EQUIPMENTDETAILCODE2
				, @coilStatusId
				, NULL
				, 0
				, GETDATE()
				, GETDATE()
				, 'T_STARTENDDATA'
				, 0
			)
				  PRINT 'INSERT INTO T_COILTRACKING '
		END
		ELSE
		BEGIN
			--更新
			UPDATE T_COILTRACKING SET
				EQUIPMENTCODE = @EQUIPMENTCODE
				, EQUIPMENTDETAILCODE1 = @EQUIPMENTDETAILCODE1
				, EQUIPMENTDETAILCODE2 = @EQUIPMENTDETAILCODE2
				, COILSTATUSID = @coilStatusId
				, UPDATETIME = GETDATE()
				, UPUSER = 'T_STARTENDDATA'
				, DELETEFLG = 0
			WHERE
				SERIALNO = @serialNo
		END
  PRINT 'UPDATE T_COILTRACKING '
	END

	CLOSE StartEndCur
	DEALLOCATE StartEndCur

	COMMIT TRANSACTION

END TRY
BEGIN CATCH
	DEALLOCATE StartEndCur
	  PRINT 'ROLLBACK TRANSACTION '
	ROLLBACK TRANSACTION
	PRINT 'エラーが発生しました： ' + CAST(ERROR_MESSAGE() AS nvarchar ) + ' L:' + CAST(ERROR_LINE() AS nvarchar)
END CATCH