								-- ALL, CEMIS or DDD, or EMISCode list
DECLARE @Schools    varchar(2000) = 'DDD';

								-- ALL, RE, MATIFIC, REFL or list
DECLARE @Programmes varchar(2000) = 'ALL';		

USE ClickDW
SET NOCOUNT ON

DECLARE @SchoolsList TABLE
(
	EMISCode	varchar(9),
	SchoolID	int
)

IF (@Schools = 'ALL')
	INSERT @SchoolsList SELECT EMISCode, SchoolID FROM ClickDW..School
IF (@Schools IN ('WC','CEMIS'))
	INSERT @SchoolsList SELECT EMISCode, SchoolID FROM ClickDW..School WHERE LEFT(EMISCode,1) = '1'
ELSE IF (@Schools IN ('NotWC','DDD'))
	INSERT @SchoolsList SELECT EMISCode, SchoolID FROM ClickDW..School WHERE LEFT(EMISCode,1) <> '1'
ELSE IF (ISNUMERIC(LEFT(@Schools,1)) = 1)
	INSERT @SchoolsList SELECT EMISCode, SchoolID FROM ClickDW..School WHERE EMISCode IN (SELECT value FROM string_split(@Schools,','));

DECLARE @ProgrammesList TABLE
(
	Programme varchar(50)
)
IF @Programmes = 'ALL'
	INSERT @ProgrammesList SELECT 'RE' UNION SELECT 'MATIFIC' UNION SELECT 'REFL'
ELSE
	INSERT @ProgrammesList SELECT value FROM string_split(@Programmes,',');

BEGIN TRY
	BEGIN TRAN

	PRINT 'ClickSTAGING..pLearnersRegisterFromSourceLearner'
	EXEC ClickSTAGING..pLearnersRegisterFromSourceLearner @ExecutionID=null, @Schools=@Schools, @Programmes=@Programmes, @Silent=1
	DECLARE @RegistrationDataImportID bigint = (SELECT MAX(DataImportID) FROM staging.DataImportDataContract)
	
	--SELECT EMISCode, Grade, COUNT(*) FROM staging.Learner WHERE DataImportID = @RegistrationDataImportID GROUP BY EMISCode, Grade ORDER BY 1, 2
	--SELECT * FROM staging.Learner WHERE DataImportID = @RegistrationDataImportID ORDER BY EMISCode, Grade, LastName, FirstName

	EXEC internal.pNewDataImportID @SourceDataImportID = @RegistrationDataImportID, @Description = 'Learner Registrations', @ExecutionID = null, @NoResults=1
	DECLARE @DataImportID bigint = (SELECT MAX(DataImportID) FROM internal.DataImport);

	IF EXISTS(SELECT * FROM staging.Learner WHERE DataImportID=@RegistrationDataImportID)
	BEGIN
		PRINT ''
		PRINT '======================================================================================================='
		PRINT 'internal.pTransferLearner'
		EXEC internal.pTransferLearner  @DataImportID=@DataImportID
	END

	--SELECT * FROM SourceLearner WHERE SourceLearnerID = '25252568'

	IF EXISTS(
		SELECT 'pTransferLearner', SL.SchoolID, SL.PersonID, COUNT(*)
		FROM ClickDW..vwSchoolLearnerYear SL
		JOIN @SchoolsList F ON SL.SchoolID = F.SchoolID
		CROSS APPLY (SELECT COUNT(*) Cnt FROM ClickDW..PersonHasprogramme PP WHERE PP.personid = SL.personid) PP
		WHERE SL.Year = 2021 AND SL.EndDate = '9999-12-31' AND PP.Cnt > 0
		GROUP BY SL.SchoolID, SL.PersonID
		HAVING COUNT(*) > 1
	)
	BEGIN
		SELECT 'pTransferLearner', SL.SchoolID, SL.PersonID, COUNT(*)
		FROM ClickDW..vwSchoolLearnerYear SL
		JOIN @SchoolsList F ON SL.SchoolID = F.SchoolID
		CROSS APPLY (SELECT COUNT(*) Cnt FROM ClickDW..PersonHasprogramme PP WHERE PP.personid = SL.personid) PP
		WHERE SL.Year = 2021 AND SL.EndDate = '9999-12-31' AND PP.Cnt > 0
		GROUP BY SL.SchoolID, SL.PersonID
		HAVING COUNT(*) > 1
	END
	
	PRINT ''
	PRINT '======================================================================================================='
	PRINT 'internal.pUpdateLearnerFromSourceLearner'
	EXEC internal.pUpdateLearnerFromSourceLearner @DataImportID=@DataImportID, @IgnoreClasses=0, @Schools=@Schools, @Programmes=@Programmes

	IF EXISTS
	(
		SELECT 'pUpdateLearnerFromSourceLearner', SL.SchoolID, SL.PersonID, COUNT(*)
		FROM ClickDW..vwSchoolLearnerYear SL
		JOIN @SchoolsList F ON SL.SchoolID = F.SchoolID
		CROSS APPLY (SELECT COUNT(*) Cnt FROM ClickDW..PersonHasprogramme PP WHERE PP.personid = SL.personid) PP
		WHERE SL.Year = 2021 AND SL.EndDate = '9999-12-31' AND PP.Cnt > 0
		GROUP BY SL.SchoolID, SL.PersonID
		HAVING COUNT(*) > 1
	)
	BEGIN
		SELECT 'pUpdateLearnerFromSourceLearner', SL.SchoolID, SL.PersonID, COUNT(*)
		FROM ClickDW..vwSchoolLearnerYear SL
		JOIN @SchoolsList F ON SL.SchoolID = F.SchoolID
		CROSS APPLY (SELECT COUNT(*) Cnt FROM ClickDW..PersonHasprogramme PP WHERE PP.personid = SL.personid) PP
		WHERE SL.Year = 2021 AND SL.EndDate = '9999-12-31' AND PP.Cnt > 0
		GROUP BY SL.SchoolID, SL.PersonID
		HAVING COUNT(*) > 1
	END

	PRINT ''
	PRINT '=======================================================================================================' 
	PRINT 'internal.pUpdateDataImport'
	EXEC internal.pUpdateDataImport @DataImportID = @DataImportID, @DataImportStatus = 'Success', @Message = '';

	--SELECT * FROM PersonHasProgramme PP WHERE PP.ProgrammeID = 9 AND PP.UserName IS NOT NULL;
	
	PRINT ''
	PRINT '======================================================================================================='
	PRINT 'internal.pGenerateUserNames'
	EXEC internal.pGenerateUserNames @Schools=@Schools, @Programmes=@Programmes
	
	IF EXISTS (SELECT * FROM @ProgrammesList WHERE Programme = 'RE')
	BEGIN
		PRINT ''
		PRINT '======================================================================================================='
		PRINT 'internal.pGetProgrammeRegistrationRegionList'
		DECLARE @RegionList TABLE
		(
			HierarchyEntityID int,
			Hierarchy varchar(200),
			RegionEntityID int,
			Region varchar(200),
			LearnerCount int,
			ClassCount int,
			ExtractDate varchar(10),
			ExtractDateTime varchar(16),
			RegionFileName varchar(200),
			ProgrammeName varchar(200),
			EmailTo varchar(1000),
			EmailCC varchar(1000)
		)

		INSERT @RegionList
		EXEC internal.pGetProgrammeRegistrationRegionList  @DataImportID=@DataImportID, @Programme='RE', @Hierarchy=NULL, @IgnoreExportedAlreadyLearners=0, @IgnoreExportedAlreadyClasses=0

		SELECT * FROM @RegionList ORDER BY Hierarchy, Region

		DECLARE @crsr_Hierarchy varchar(200);
		DECLARE @crsr_Region varchar(200);
		DECLARE crsr CURSOR LOCAL FOR SELECT Hierarchy, Region FROM @RegionList ORDER BY Hierarchy, Region
		OPEN Crsr
		FETCH NEXT FROM crsr INTO @crsr_Hierarchy, @crsr_Region
		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC internal.pExtractProgrammeRegistrationRegionLearnerListReadingEggs @DataImportID=@DataImportID, @Hierarchy=@crsr_Hierarchy, @Region=@crsr_Region
			FETCH NEXT FROM crsr INTO @crsr_Hierarchy, @crsr_Region
		END
		CLOSE crsr;
		DEALLOCATE crsr
	END

	IF EXISTS (SELECT * FROM @ProgrammesList WHERE Programme = 'REFL')
	BEGIN
		EXEC internal.pExtractReflectiveLearningRegistrationLearnerList 'South Africa'
	END

	ROLLBACK TRAN
END TRY
BEGIN CATCH
	ROLLBACK TRAN;
	THROW;
END CATCH
GO

