-- PROCEDURE: internal.plearnerregistrationcheck(character varying, character varying)

-- DROP PROCEDURE IF EXISTS internal.plearnerregistrationcheck(character varying, character varying);

CREATE OR REPLACE PROCEDURE internal.plearnerregistrationcheck(
	par_schools character varying,
	par_programmes character varying)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
 var_RegistrationDataImportID BIGINT; 
 var_DataImportID BIGINT;
 v_crsr_Hierarchy varchar(200);
 v_crsr_Region varchar(200);
 var_value varchar(200);
 var_year int;
 DECLARE crsr cursor  FOR SELECT Hierarchy, Region FROM tmp_RegionList ORDER BY Hierarchy, Region;

BEGIN
	drop table if exists tmp_schoolslist;
	drop table if exists tmp_programmeslist;
	
	CREATE TEMPORARY TABLE tmp_SchoolsList
	(
		EMISCode	varchar(9),
		SchoolID	int
	);
		var_year = EXTRACT(YEAR FROM (SELECT CURRENT_DATE));
		Raise Notice '=======================================================================================================';
		Raise Notice 'Begin';
		RAISE NOTICE 'Year: %',var_year;
	IF(par_Schools = 'ALL') THEN
		Raise Notice 'ALL';
		INSERT INTO tmp_SchoolsList  SELECT EMISCode, SchoolID FROM dbo.School; 
	END IF;
	
	
	IF(par_Schools IN ('WC','CEMIS')) THEN
		INSERT INTO tmp_SchoolsList SELECT EMISCode, SchoolID FROM dbo.School WHERE LEFT(EMISCode,1) = '1';
	END IF;
	

	
	IF (par_Schools IN ('NotWC','DDD')) THEN
		INSERT into tmp_SchoolsList SELECT EMISCode, SchoolID FROM dbo.School WHERE LEFT(EMISCode,1) <> '1';
	END IF;
	var_value = LEFT(par_Schools,1);
	If (select var_value ~ '^[0-9\.]+$') THEN
	   if(var_value=1) THEN
	   	 INSERT into tmp_SchoolsList SELECT EMISCode, SchoolID FROM dbo.School WHERE EMISCode IN (SELECT value FROM string_split(par_Schools,','));
	   end if;
	END IF;
	
	Raise Notice 'Loaded Schools list';
	
		CREATE TEMPORARY TABLE tmp_ProgrammesList
		(
			Programme varchar(50)
		);
		
	IF par_Programmes = 'ALL' THEN
		INSERT INTO tmp_ProgrammesList SELECT 'RE' UNION SELECT 'MATIFIC' UNION SELECT 'REFL';
	ELSE
		INSERT INTO tmp_ProgrammesList SELECT value FROM string_split(par_Programmes,',');
	END IF;

	Raise Notice 'ClickSTAGING..pLearnersRegisterFromSourceLearner';
	
	Select Max(DataImportID) INTO var_RegistrationDataImportID FROM dbo.dataimportdatacontract_staging;
	
	Raise Notice 'DataImportID: %', var_RegistrationDataImportID;
	Raise Notice 'par_Schools: %', par_Schools;
	Raise Notice 'par_Programmes: %', par_Programmes;
	-- CALL dbo.plearnersregisterfromsourcelearner(null, par_Schools, par_Programmes, 1,var_RegistrationDataImportID);
	CALL dbo.plearnersregisterfromsourcelearner(null, par_Schools, par_Programmes, 1);
	Raise Notice 'Passed';
	
	Select MAX(DataImportID) INTO var_DataImportID   FROM internal.DataImport;
	-- CALL internal.pnewdataimportid(var_RegistrationDataImportID,'Learner Registrations', null,1,var_DataImportID);
	RAISE NOTICE 'Before internal.pnewdataimportid';
	perform * from internal.pnewdataimportid(var_RegistrationDataImportID,'Learner Registrations', null, 1);
	
		IF EXISTS(SELECT * FROM dbo.learner_staging WHERE DataImportID=var_RegistrationDataImportID)
	THEN
		Raise Notice '';
		Raise Notice '=======================================================================================================';
		Raise Notice 'internal.pTransferLearner: %',var_DataImportID;
		CALL internal.pTransferLearner(var_DataImportID);
	END IF;
	
	 IF EXISTS(		
		SELECT 'pTransferLearner', SL.SchoolID, SL.PersonID, COUNT(*)
		FROM dbo.vwSchoolLearnerYear SL
		JOIN tmp_SchoolsList F ON SL.SchoolID = F.SchoolID
		CROSS JOIN LATERAL (SELECT COUNT(*) Cnt FROM dbo.PersonHasprogramme PP WHERE PP.personid = SL.personid) PP
		WHERE SL.Year = var_year AND SL.EndDate = '9999-12-31' AND PP.Cnt > 0
		GROUP BY SL.SchoolID, SL.PersonID
		HAVING COUNT(*) > 1		
	)
	THEN	
		PERFORM 'pTransferLearner', SL.SchoolID, SL.PersonID, COUNT(*)
		FROM dbo.vwSchoolLearnerYear SL
		JOIN tmp_SchoolsList F ON SL.SchoolID = F.SchoolID
		CROSS JOIN LATERAL (SELECT COUNT(*) Cnt FROM dbo.PersonHasprogramme PP WHERE PP.personid = SL.personid) PP
		WHERE SL.Year = var_year AND SL.EndDate = '9999-12-31' AND PP.Cnt > 0
		GROUP BY SL.SchoolID, SL.PersonID
		HAVING COUNT(*) > 1;
	END IF;
	
	Raise Notice '';
	Raise Notice '=======================================================================================================';
	Raise Notice 'internal.pUpdateLearnerFromSourceLearner';
	RAISE NOTICE 'PARAMS %,%,0,0,0,0 ,%, %',var_DataImportID,var_year,par_Schools,par_Programmes;
	CALL internal.pupdatelearnerfromsourcelearner(var_DataImportID, var_year, 0, 0, 0, 0, par_Schools, par_Programmes);
/*	
	
	
	IF EXISTS(
		
		SELECT 'pUpdateLearnerFromSourceLearner', SL.SchoolID, SL.PersonID, COUNT(*)
		FROM dbo.vwSchoolLearnerYear SL 
	    JOIN tmp_SchoolsList F ON SL.SchoolID = F.SchoolID 
		JOIN lateral((SELECT COUNT(*) Cnt FROM ClickDW.PersonHasprogramme PP
		   WHERE PP.personid = SL.personid AND SL.Year = 2021 AND SL.EndDate = '9999-12-31' AND PP.Cnt > 0) 
		) b on SL.personid = b.personid
		GROUP BY SL.SchoolID, SL.PersonID
		HAVING COUNT(*) > 1
		
	)
	THEN
	
		SELECT 'pUpdateLearnerFromSourceLearner', SL.SchoolID, SL.PersonID, COUNT(*)
		FROM dbo.vwSchoolLearnerYear SL 
	    JOIN tmp_SchoolsList F ON SL.SchoolID = F.SchoolID 
		JOIN lateral((SELECT COUNT(*) Cnt FROM ClickDW.PersonHasprogramme PP
		   WHERE PP.personid = SL.personid AND SL.Year = 2021 AND SL.EndDate = '9999-12-31' AND PP.Cnt > 0) 
		) b on SL.personid = b.personid
		GROUP BY SL.SchoolID, SL.PersonID
		HAVING COUNT(*) > 1;
	
	END IF;
	
	Raise Notice '';
	Raise Notice '=======================================================================================================';
	Raise Notice 'internal.pUpdateDataImport';
	CALL internal.pUpdateDataImport(var_DataImportID, 'Success', '');

	--SELECT * FROM PersonHasProgramme PP WHERE PP.ProgrammeID = 9 AND PP.UserName IS NOT NULL;
	
	Raise Notice '';
	Raise Notice '=======================================================================================================';
	Raise Notice 'internal.pGenerateUserNames';
	CALL internal.pGenerateUserNames(par_Schools, par_Programmes);
	
	IF EXISTS (select * FROM tmp_ProgrammesList WHERE Programme = 'RE') p
	THEN
		Raise Notice '';
		Raise Notice '=======================================================================================================';
		Raise Notice 'internal.pGetProgrammeRegistrationRegionList';
		
		CREATE TEMPORARY TABLE tmp_RegionList
		(
			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 INTO tmp_RegionList (select * FROM tmp_ProgrammesList WHERE Programme = 'RE');
		CALL internal.pGetProgrammeRegistrationRegionList(v_DataImportID,'RE', NULL, 0, 0);
		
		SELECT * FROM tmp_RegionList ORDER BY Hierarchy, Region;
		
			
		OPEN crsr;
		FETCH crsr INTO v_crsr_Hierarchy, v_crsr_Region;
		WHILE FOUND
		LOOP
			CALL internal.pExtractProgrammeRegistrationRegionLearnerListReadingEggs(var_DataImportID, v_crsr_Hierarchy, v_crsr_Region);
			FETCH crsr INTO v_crsr_Hierarchy, v_crsr_Region;
		END LOOP;
		CLOSE crsr;
		DEALLOCATE crsr;
	END IF;
	
		IF EXISTS (SELECT * FROM tmp_ProgrammesList WHERE Programme = 'REFL')
	THEN
		CALL internal.pExtractReflectiveLearningRegistrationLearnerList('South Africa');
	END IF;
	
	EXCEPTION
                WHEN OTHERS THEN
				Raise Exception 'Error';
				ROLLBACK;
	Raise Notice 'Done';
*/	
	drop table if exists tmp_schoolslist;
	drop table if exists tmp_programmeslist;
	END;
	
$BODY$;
