SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION FN取得選定場地編號 
(
	@課程企劃編號 AS INT
)
RETURNS INT
AS
BEGIN
	DECLARE @RESULT INT
	DECLARE @COUNT INT
	DECLARE @場地編號 INT

	SELECT @COUNT=COUNT(1) FROM 訓練課程企劃 WHERE 編號=@課程企劃編號
	IF @COUNT = 0
	BEGIN
	  RETURN 0
	END
	ELSE
	BEGIN
	  SELECT TOP 1 @場地編號 = 場地編號 FROM 場地異動 
	  WHERE 課程企劃編號 = @課程企劃編號
	  ORDER BY 異動次數 DESC
	  
	  IF @場地編號 IS NOT NULL
	  BEGIN
		RETURN @場地編號
	  END
	  ELSE
	  BEGIN
		DECLARE @場地A INT
		DECLARE @場地B INT
		DECLARE @場地C INT
		DECLARE @分數A INT
		DECLARE @分數B INT
		DECLARE @分數C INT
	    
		SELECT @場地A = ISNULL(場地編號_A, 0), @場地B = ISNULL(場地編號_B, 0), @場地C = ISNULL(場地編號_C, 0),
		  @分數A = ISNULL(合計_A, 0), @分數B = ISNULL(合計_B, 0), @分數C = ISNULL(合計_C, 0)
		FROM 場地遴選
		WHERE 課程企劃編號 = @課程企劃編號
	    
		IF (@分數A > @分數B AND @分數A > @分數C)
		  SET @場地編號 = @場地A
		ELSE IF (@分數B > @分數A AND @分數B > @分數C)
		  SET @場地編號 = @場地B
		ELSE
		  SET @場地編號 = @場地C
	  END
	END
	RETURN @場地編號
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION FN取得選定講師編號 
(
	@課程企劃編號 AS INT,
	@課程序號 AS INT
)
RETURNS INT
AS
BEGIN
	DECLARE @RESULT INT
	DECLARE @COUNT INT
	DECLARE @講師編號 INT

	SELECT @COUNT=COUNT(1) FROM 訓練課程內容 WHERE 訓練課程企劃編號=@課程企劃編號 AND 課程序號=@課程序號
	IF @COUNT = 0
	BEGIN
	  RETURN 0
	END
	ELSE
	BEGIN
	  SELECT TOP 1 @講師編號 = 講師編號 FROM 講師異動 
	  WHERE 課程企劃編號 = @課程企劃編號 AND 課程序號 = @課程序號
	  ORDER BY 異動次數 DESC
	  
	  IF @講師編號 IS NOT NULL
	  BEGIN
		RETURN @講師編號
	  END
	  ELSE
	  BEGIN
		DECLARE @講師A INT
		DECLARE @講師B INT
		DECLARE @講師C INT
		DECLARE @分數A INT
		DECLARE @分數B INT
		DECLARE @分數C INT
	    
		SELECT @講師A = ISNULL(講師編號_A, 0), @講師B = ISNULL(講師編號_B, 0), @講師C = ISNULL(講師編號_C, 0),
		  @分數A = ISNULL(合計_A, 0), @分數B = ISNULL(合計_B, 0), @分數C = ISNULL(合計_C, 0)
		FROM 講師遴選
		WHERE 課程企劃編號 = @課程企劃編號 AND 課程序號 = @課程序號
	    
		IF (@分數A > @分數B AND @分數A > @分數C)
		  SET @講師編號 = @講師A
		ELSE IF (@分數B > @分數A AND @分數B > @分數C)
		  SET @講師編號 = @講師B
		ELSE
		  SET @講師編號 = @講師C
	  END
	END
	RETURN @講師編號
END
GO

grant EXECUTE ON FN取得選定場地編號 TO HR
grant EXECUTE ON FN取得選定講師編號 to HR