DELIMITER //
CREATE OR REPLACE PROCEDURE initiate_multi_load_monthly_over_under_budget_data() 
	
	/*NOTE: SJH 5/31/2023 - This proc will be used to call load_monthly_over_under_budget_data for multiple years at one execution.
		It will call the proc for the months 11 - 6 (Per Alex) for each of the most current 7 years up to the current one.*/
		
	/*It is not dynamic now for the year, so to use this change the fiscal_year assignment on line 32 to be the desired year.
		Then call this proc to load all months for all orgs.
		The code has not yet been created to use yearTab for dynamic table names. This may not be necessary because the data will be
		migrated each month.*/
		
BEGIN
	DECLARE current_year INT;
	DECLARE current_month INT;
	DECLARE fiscal_year INT;
	DECLARE param_org_id INT;	
	DECLARE test_flag VARCHAR(255);
	
	/*WHEN set to 1, the cursor loop will end.*/
	DECLARE finished INTEGER DEFAULT 0;
	
	/*DECLARE years_cursor CURSOR FOR SELECT year_id FROM school_years WHERE year_id <= 23 AND year_id >= 16;*/	
	
	DECLARE orgs_cursor CURSOR FOR SELECT org_id FROM organizations
	WHERE organization_types_id IN(1,2,3,4,5) /*IN(1, 2, 3, 4, 5, 6)NOT IN(7, 9)*/
	AND client_status = 'A';
	/*AND org_id NOT IN(12, 50, 56, 58, 303, 1592, 1599, 1604, 1613, 1874, 1875, 1909, 1911)*//*303 works for nov and dec only*/ /*org_id NOT IN(9, 48, 54, 113, 1859) for 2019 - - 113 has null commencement_date. All of these went through an upgrade in 2022.
		I do not yet know why the others cause errors. Need to check later.*/
	
	/* declare NOT FOUND handler */
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
	
		
	
	SET fiscal_year = 24;	
	
	OPEN orgs_cursor;
		getOrgs: LOOP
			FETCH orgs_cursor INTO param_org_id;
			
			/*CAll load_monthly_over_under_budget_data(fiscal_year, 7, param_org_id);
			CAll load_monthly_over_under_budget_data(fiscal_year, 8, param_org_id);
			CAll load_monthly_over_under_budget_data(fiscal_year, 9, param_org_id);
			CAll load_monthly_over_under_budget_data(fiscal_year, 10, param_org_id);*/
			CAll load_monthly_over_under_budget_data(fiscal_year, 11, param_org_id);
			/*CAll load_monthly_over_under_budget_data(fiscal_year, 12, param_org_id);
			CAll load_monthly_over_under_budget_data(fiscal_year, 1, param_org_id);			
			CAll load_monthly_over_under_budget_data(fiscal_year, 2, param_org_id);
			CAll load_monthly_over_under_budget_data(fiscal_year, 3, param_org_id);
			CAll load_monthly_over_under_budget_data(fiscal_year, 4, param_org_id);
			CAll load_monthly_over_under_budget_data(fiscal_year, 5, param_org_id);
			CAll load_monthly_over_under_budget_data(fiscal_year, 6, param_org_id);*/
			
			
			IF finished = 1 THEN
				LEAVE getOrgs;			
			END IF;
			
		END LOOP getOrgs;
	
	CLOSE orgs_cursor; 	
	
END //
DELIMITER ;

CALL initiate_multi_load_monthly_over_under_budget_data()