-- USE Vue

IF DB_NAME() NOT IN ('VueDev', 'VueTest', 'VueRpt', 'VueMaint', 'Vue')
	BEGIN

    	USE tempdb
    	RAISERROR('You are in the wrong database.  This script must be run in the Vue database.', 16, 1);
    	RETURN

    	END
    GO

IF OBJECT_ID('dbo.vspGetBiometricsForGlobalAdd', 'P') IS NOT NULL
        BEGIN

        DROP PROCEDURE dbo.vspGetBiometricsForGlobalAdd

        END
    GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[vspGetBiometricsForGlobalAdd]
	@BiometricTypeCode			VARCHAR(4),
	@RegistrationIDsString		VARCHAR(MAX)
AS
/*************************************************************************************************
 *
 * Name:
 *
 *      vspGetBiometricsForGlobalAdd
 *
 * Input Parameters:
 *		@BiometricTypeCode		- The type of biometric to search for.
 *									(See ExamDeliveryBiometric.BiometricTypeCode)
 *		@RegistrationIDsString		- Comma-delimited list of registration IDs (i.e. OrderItemIDs)
 *
 * Description:
 *
 *      For a given list of registration IDs, retrieves the ExamDeliveryBiometric records to be
 *		used to add biometrics for those registrations to the Global Datastore (for 1:N comparison).
 *
 *		Returns only those biometrics with BiometricTypeCode set to @BiometricTypeCode.
 *		Returns only those biometrics with BiometricCaptureContextCode set to 'ADMT'.
 *      Returns only those biometrics that exist in the file system.  (have not been deleted.)
 *
 *		May return zero or one record for each registration ID.  If there is more than one record
 *		that matches the criteria, this will return only one: the one with the most recent
 *		ExamDeliveryBiometric.CaptureDate.
 *
 * Returns:
 *
 *     ExamDeliveryBiometric.*
 *
 * Revision History:
 *
 *      12/09/2010 - Marnee DeRider
 *      Initial release.
 *
 *      2/28/2011 - Steve Hummel
 *      Updated to include ExamDeliveryBiometric.IsBiometricFileDeleted = 0 to exclude deleted biometrics
 *
 *		09/08/2011 - mckibta
 *		Rewritten for performance improvement
 *
 *
 * Copyright © 2010 Pearson VUE, Inc. All rights reserved.
 *
 *************************************************************************************************/

BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	DECLARE @Command VARCHAR(MAX)

	SET @Command = '
	SELECT
		LatestCapturedBiometric.ExamDeliveryBiometricID,
		LatestCapturedBiometric.OrderItemID,
		LatestCapturedBiometric.BiometricTypeCode,
		LatestCapturedBiometric.BiometricCaptureContextCode,
		LatestCapturedBiometric.CaptureDate,
		LatestCapturedBiometric.BiometricPath,
		LatestCapturedBiometric.Operator_ID,
		LatestCapturedBiometric.AppName,
		LatestCapturedBiometric.LastUpdate,
		LatestCapturedBiometric.LastUpdateAppUserID,
		LatestCapturedBiometric.IsBiometricFileDeleted
	FROM
		(
			SELECT
				ExamDeliveryBiometric.ExamDeliveryBiometricID,
				ExamDeliveryBiometric.OrderItemID,
				ExamDeliveryBiometric.BiometricTypeCode,
				ExamDeliveryBiometric.BiometricCaptureContextCode,
				ExamDeliveryBiometric.CaptureDate,
				ExamDeliveryBiometric.BiometricPath,
				ExamDeliveryBiometric.Operator_ID,
				ExamDeliveryBiometric.AppName,
				ExamDeliveryBiometric.LastUpdate,
				ExamDeliveryBiometric.LastUpdateAppUserID,
				ExamDeliveryBiometric.IsBiometricFileDeleted,
				Ranking = ROW_NUMBER() OVER(PARTITION BY ExamDeliveryBiometric.OrderItemID ORDER BY ExamDeliveryBiometric.CaptureDate DESC)
			FROM dbo.ExamDeliveryBiometric
			WHERE ExamDeliveryBiometric.BiometricTypeCode = ''' + @BiometricTypeCode + '''
				AND ExamDeliveryBiometric.BiometricCaptureContextCode = ''ADMT''
				AND ExamDeliveryBiometric.IsBiometricFileDeleted = 0
				AND ExamDeliveryBiometric.OrderItemID IN (SELECT ' + REPLACE(@RegistrationIDsString, ',', ' UNION ALL SELECT ') + ')
		) AS LatestCapturedBiometric
	WHERE LatestCapturedBiometric.Ranking = 1
	'

	EXECUTE (@Command)

END

GO
GRANT EXECUTE ON [dbo].[vspGetBiometricsForGlobalAdd] TO [ReadPublicData] AS [dbo]
GO
GRANT EXECUTE ON [dbo].[vspGetBiometricsForGlobalAdd] TO [VTSAppUsers] AS [dbo]
GO