USE [Clancy_Exp_Database]
GO
/****** Object:  StoredProcedure [dbo].[usp_CalculateInterceptDriver]    Script Date: 03/09/2010 07:30:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:		Peter Wilson
-- Create date: 12/10/2007
-- Description:	Feeds a parameter lists to usp_CalculateInterceptByHole
--              Then updates the final intercept table with the name of the respective runs
-- =============================================
CREATE PROCEDURE [dbo].[usp_CalculateInterceptDriver] 
	@Dataset varchar(30), 
	@PackageID int,
	@Owner varchar(50)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @SQL varchar(1000)
	DECLARE @Hole_ID varchar(30)
	DECLARE @MinCutoff real
	DECLARE @MaxCutoff real
	DECLARE @MinIntercept real
	DECLARE @MaxInternalWaste real
	DECLARE @Element varchar(20)
	DECLARE @CoElement1 varchar(20)
	DECLARE @CoElement2 varchar(20)
	DECLARE @CoElement3 varchar(20)
	DECLARE @CoElement4 varchar(20)
	DECLARE @CoElement5 varchar(20)
	DECLARE @CoElement6 varchar(20)

	--Delete all previous records from tblDHInterceptsCustom for this owner, package and dataset combination
	Set @SQL = 'Delete from tblDHInterceptsCustom where Dataset = ''' + @Dataset + ''' and PackageID = ' + Cast(@PackageID as varchar) + ' and Owner = ''' + @Owner + ''';'
	EXEC(@SQL);



	SET @SQL = 'DECLARE DHInterceptParams ' 
    SET @SQL = @SQL + 'CURSOR FOR SELECT Hole_ID, Element, MinCutOff, MaxCutOff, MinIntercept, MaxInternalWaste, CoElement1Name, CoElement2Name, CoElement3Name, CoElement4Name, CoElement5Name, CoElement6Name ' 
    SET @SQL = @SQL + 'FROM tblDHInterceptParams i INNER JOIN tblDHColl c on c.Dataset = ''' + @Dataset + ''' and i.PackageID = ' + Cast(@PackageID as varchar) + ';'
	EXEC(@SQL);
	OPEN DHInterceptParams;

	FETCH NEXT FROM DHInterceptParams INTO @Hole_ID, @Element, @MinCutOff, @MaxCutOff, @MinIntercept, @MaxInternalWaste, @CoElement1, @CoElement2, @CoElement3, @CoElement4, @CoElement5, @CoElement6;		

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXECUTE dbo.usp_CalculateInterceptByHole @Owner, @PackageID,@Dataset,@Hole_ID,@MinCutoff,@MaxCutoff,@MinIntercept,@MaxInternalWaste,@Element,@CoElement1,@CoElement2,@CoElement3, @CoElement4, @CoElement5, @CoElement6;
		FETCH NEXT FROM DHInterceptParams INTO @Hole_ID, @Element, @MinCutOff, @MaxCutOff, @MinIntercept, @MaxInternalWaste, @CoElement1, @CoElement2, @CoElement3, @CoElement4, @CoElement5, @CoElement6;		
	END /*End of loop*/
	CLOSE DHInterceptParams;
	DEALLOCATE DHInterceptParams;
	UPDATE tblDHInterceptsCustom  SET tblDHInterceptsCustom.PackageName = tblDHInterceptParams.PackageName FROM tblDHInterceptParams INNER JOIN tblDHInterceptsCustom  ON (tblDHInterceptParams.PackageID = tblDHInterceptsCustom.PackageID);
END

/****** Object:  StoredProcedure [dbo].[usp_CalculateInterceptByHole_eval]    Script Date: 10/09/2008 14:52:50 ******/
SET ANSI_NULLS ON