CREATE OR REPLACE PROCEDURE internal.pcompletedddfileimport()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
	return_code integer DEFAULT 0;
	var_Commit boolean = 0;
	var_SourceDataImportID BIGINT = (SELECT DataImportID FROM dbo.dataimportdatacontract_staging 
								   WHERE DataSourceTypeID = 8 AND DataImportStatus = 'Success' ORDER BY 1 DESC LIMIT 1);
BEGIN

	
	Raise Notice '================================';
	Raise Notice 'internal.pUpdateDDDIDs';
	Raise Notice '%', var_SourceDataImportID;
	CALL internal.pUpdateDDDIDs(var_SourceDataImportID);

	Raise Notice '';
	Raise Notice '==============================';
	Raise Notice 'internal.pUpdateSourceLearner';
	CALL internal.pUpdateSourceLearner(var_SourceDataImportID,2,return_code);
	Raise Notice 'called pUdate';

	IF EXISTS
	(
		SELECT 'pUpdateSourceLearner', SL.PersonID, SL.SchoolID, COUNT(SL.SchoolLearnerID)
		FROM dbo.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
	)
	THEN
		
		SELECT 'pUpdateSourceLearner', SL.PersonID, SL.SchoolID, COUNT(SL.SchoolLearnerID)
		FROM dbo.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 IF;
	
	Raise Notice '';
	Raise Notice '=======================================================================================================';
	Raise Notice 'internal.pUpdateLearnerFromSourceLearnerFirstUsableData';
	CALL internal.pUpdateLearnerFromSourceLearnerFirstUsableData(var_SourceDataImportID);
	
	
	
	IF EXISTS
	(
		SELECT 'pUpdateLearnerFromSourceLearnerFirstUsableData', SL.PersonID, SL.SchoolID, COUNT(SL.SchoolLearnerID)
		FROM dbo.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
	)
	THEN
	
		SELECT 'pUpdateLearnerFromSourceLearnerFirstUsableData', SL.PersonID, SL.SchoolID, COUNT(SL.SchoolLearnerID)
		FROM dbo.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 IF;
	Raise Notice 'Finished';
	EXCEPTION
                WHEN OTHERS THEN
				Raise Exception 'Error';
				ROLLBACK;
	Raise Notice 'Finished';
END;
$BODY$;
