USE ClickSTAGING

--SELECT TOP 10 * FROM ClickSTAGING..DataImportDataContract WHERE DataSourceTypeID = 8 AND DataImportStatus = 'Success' ORDER BY 1 DESC

--DECLARE @SourceDataImportID bigint = 3733
DECLARE @SourceDataImportID bigint = (SELECT TOP 1 DataImportID FROM ClickSTAGING..DataImportDataContract WHERE DataSourceTypeID = 8 AND DataImportStatus = 'Success' ORDER BY 1 DESC);
DECLARE @Commit bit = 0

BEGIN TRY
	BEGIN TRAN


	PRINT ''
	PRINT '======================================================================================================='
	PRINT 'ClickDW.internal.pUpdateDDDIDs'
	EXEC ClickDW.internal.pUpdateDDDIDs @SourceLearnerDataImportID=@SourceDataImportID

	PRINT ''
	PRINT '======================================================================================================='
	PRINT 'ClickDW.internal.pUpdateSourceLearner'
	EXEC ClickDW.internal.pUpdateSourceLearner @SourceDataImportID=@SourceDataImportID, @UpdatePersonID=2

	IF EXISTS
	(
		SELECT 'pUpdateSourceLearner', SL.PersonID, SL.SchoolID, COUNT(SL.SchoolLearnerID)
		FROM ClickDW..vwSchoolLearnerYear SL
		WHERE SL.Year IN (2020,2021) AND SL.EndDate = '9999-12-31'
		GROUP BY SL.PersonID, SL.SchoolID
		HAVING COUNT(DISTINCT SL.SchoolLearnerID) > 1
	)
	BEGIN
		SELECT 'pUpdateSourceLearner', SL.PersonID, SL.SchoolID, COUNT(SL.SchoolLearnerID)
		FROM ClickDW..vwSchoolLearnerYear SL
		WHERE SL.Year IN (2020,2021) AND SL.EndDate = '9999-12-31'
		GROUP BY SL.PersonID, SL.SchoolID
		HAVING COUNT(DISTINCT SL.SchoolLearnerID) > 1
	END
	--SELECT COUNT(*) FROM ClickDW..SourceLearner WHERE SourceDataImportID=3527

	--SELECT * FROM ClickDW..SourceLearner WHERE MappedPersonID IN (1174492,1174596)

	PRINT ''
	PRINT '======================================================================================================='
	PRINT 'ClickDW.internal.pUpdateLearnerFromSourceLearnerFirstUsableData'
	EXEC ClickDW.internal.pUpdateLearnerFromSourceLearnerFirstUsableData @SourceDataImportID=@SourceDataImportID

	--SELECT * FROM ClickDW..SourceLearner WHERE MappedPersonID IN (1174492,1174596)

	IF EXISTS
	(
		SELECT 'pUpdateLearnerFromSourceLearnerFirstUsableData', SL.PersonID, SL.SchoolID, COUNT(SL.SchoolLearnerID)
		FROM ClickDW..vwSchoolLearnerYear SL
		WHERE SL.Year IN (2020,2021) AND SL.EndDate = '9999-12-31'
		GROUP BY SL.PersonID, SL.SchoolID
		HAVING COUNT(DISTINCT SL.SchoolLearnerID) > 1
	)
	BEGIN
		SELECT 'pUpdateLearnerFromSourceLearnerFirstUsableData', SL.PersonID, SL.SchoolID, COUNT(SL.SchoolLearnerID)
		FROM ClickDW..vwSchoolLearnerYear SL
		WHERE SL.Year IN (2020,2021) AND SL.EndDate = '9999-12-31'
		GROUP BY SL.PersonID, SL.SchoolID
		HAVING COUNT(DISTINCT SL.SchoolLearnerID) > 1;
	END

	--PRINT ''
	--PRINT '======================================================================================================='
	--PRINT 'ClickDW.internal.pUpdateSchoolDataSource'
	--EXEC ClickDW.internal.pUpdateSchoolDataSource

	IF @Commit = 1 
	BEGIN
		PRINT 'Committing'
		COMMIT TRAN
	END
	ELSE
	BEGIN
		PRINT 'Undoing'
		ROLLBACK TRAN
	END
END TRY
BEGIN CATCH
	PRINT 'Undoing';
	ROLLBACK TRAN;
	THROW
END CATCH
