DELIMITER //
CREATE OR REPLACE PROCEDURE load_monthly_over_under_budget_data(in param_fiscal_year integer, in param_month integer, in param_org integer)
	
	/*NOTE: SJH 5/31/2023 - This file will be called by a cron job. It will insert a record for each org at the end of every month.
        The reason for this is to save data that will be used in the graph on the volumes_cost pages. It would be too time
        consuming to get the data every time a user visits that page, because there are too many conditions that can change the
        values of the projected volumes and costs.*/
		
		/*This is not dynamic now for the year, so to use this change the fiscal_year assignment on line 32 in the
			initiate_multi_load_monthly_over_under_budget_data() procedure to be the desired year, then
			replace all of the 9 table names in this procedure (proc_load_monthly_over_under_budget_data) to the
			same year. Then call initiate_multi_load_monthly_over_under_budget_data() to load all months for all orgs.*/
		
BEGIN	
    DECLARE calendar_year INT;
	DECLARE current_year INT;
	DECLARE month_name VARCHAR(10);    
    DECLARE yearTab VARCHAR(11);
	DECLARE fiscal_days DECIMAL(14, 11);
	DECLARE org_fiscal_days DECIMAL(14, 11);
	DECLARE cost_increase_multiplier DECIMAL(4, 3);
	DECLARE org_cm_diff DECIMAL(15, 12);
	DECLARE last_diff DECIMAL(17, 14);
    DECLARE cm_diff DECIMAL(17, 14);
	DECLARE reporting VARCHAR(1);
	DECLARE closeout_date_var DATETIME;
	DECLARE ser_num VARCHAR(25);	
	DECLARE building_name_var VARCHAR(65);
	
	DECLARE student_pop_tot INT DEFAULT 0;
	DECLARE avg_blk_per_student_var INT DEFAULT 0;
	DECLARE avg_col_per_student_var INT DEFAULT 0;	

	DECLARE cpc_black_var DECIMAL(7, 6) DEFAULT 0;
	DECLARE cpc_color_var DECIMAL(7, 6) DEFAULT 0;	
	
	/*Timestamp formatted dates*/
    DECLARE timeline_date TIMESTAMP;
	DECLARE starting_date TIMESTAMP;
    
    /*DECLARE notReportingDateFormat TIMESTAMP;*/
	DECLARE meter_created_date TIMESTAMP;	
	
	/*Unix timestamps*/
	DECLARE timelineDate INT;
	DECLARE startingDate INT;
    DECLARE endingDate INT;
	DECLARE endingDateForProjCalcs INT;
    DECLARE FiscalYearEndDateUnix INT;
	DECLARE notReportingDate INT;
	DECLARE lastMeterDate INT;
	DECLARE closeoutDateUnix INT;
	DECLARE deviceCommencementDate INT;
	
	/*The raw db value w/o the value being modified conditonally.*/
	DECLARE org_commencement_date_raw INT;
	
	/*The date value that starts as a raw value from the db, but can change in code based on conditions*/
	DECLARE org_commencement_date INT;	
	
	
	/*Volumes variables*/
	DECLARE consumed_black_vol INT DEFAULT 0;
	DECLARE consumed_color_vol INT DEFAULT 0;
	DECLARE budgeted_black_var INT DEFAULT 0;
	DECLARE budgeted_color_var INT DEFAULT 0;
	
	DECLARE budgeted_blk_var INT DEFAULT 0;
	DECLARE budgeted_col_var INT DEFAULT 0;
	DECLARE projected_black_var INT DEFAULT 0;
	DECLARE projected_color_var INT DEFAULT 0;
	DECLARE proj_blk_year_next INT DEFAULT 0;
	DECLARE proj_col_year_next INT DEFAULT 0;	
	
	
	/*Costs variables*/
	DECLARE consumed_black_cost_var DECIMAL(10, 2) DEFAULT 0;
	DECLARE consumed_color_cost_var DECIMAL(10, 2) DEFAULT 0;
	DECLARE budgeted_black_cost INT DEFAULT 0;
	DECLARE budgeted_color_cost INT DEFAULT 0;
	DECLARE projected_black_cost INT DEFAULT 0;
	DECLARE projected_color_cost INT DEFAULT 0;
	DECLARE proj_blk_year_next_cost DECIMAL(10, 2) DEFAULT 0;
	DECLARE proj_col_year_next_cost DECIMAL(10, 2) DEFAULT 0;
	
		
	/*Costs totals variables*/
	DECLARE consumed_black_tot_cost DECIMAL(10, 2) DEFAULT 0;
	DECLARE consumed_color_tot_cost DECIMAL(10, 2) DEFAULT 0;
	DECLARE budgeted_black_tot_cost DECIMAL(10, 2) DEFAULT 0;
	DECLARE budgeted_color_tot_cost DECIMAL(10, 2) DEFAULT 0;
	DECLARE projected_black_tot_cost INT DEFAULT 0;
	DECLARE projected_color_tot_cost INT DEFAULT 0;
	DECLARE proj_blk_year_next_tot_cost DECIMAL(10, 2) DEFAULT 0;
	DECLARE proj_col_year_next_tot_cost DECIMAL(10, 2) DEFAULT 0;
	
	/*These variables will be used to conditionally calculate the projected values.*/
	DECLARE consumed_black_estimated INT;
    DECLARE consumed_black_estimated_cost INT;
	DECLARE consumed_color_estimated INT;
    DECLARE consumed_color_estimated_cost INT;	
	
	DECLARE over_under_budget_tot DECIMAL(10, 2) DEFAULT 0;

	
	/*Variables to hold data for machines that have been traded.*/
	DECLARE org_trade_out_id INT(11);	
    DECLARE trade_out_cons_blk INT DEFAULT 0;
    DECLARE trade_out_cons_col INT DEFAULT 0;
    DECLARE trade_out_cons_blk_cost DECIMAL(10, 2) DEFAULT 0;
    DECLARE trade_out_cons_col_cost DECIMAL(10, 2) DEFAULT 0;
    DECLARE avg_blk_per_student_trade_out INT DEFAULT 0;
    DECLARE avg_col_per_student_trade_out INT DEFAULT 0;
	DECLARE avg_blk_per_student_trade_out_cost DECIMAL(10, 2) DEFAULT 0;
    DECLARE avg_col_per_student_trade_out_cost DECIMAL(10, 2) DEFAULT 0;
	
	
	/*WHEN set to 1, the cursor loop will end. */
	DECLARE finished INTEGER DEFAULT 0;	
	
	
	DECLARE data_cursor CURSOR FOR SELECT
				
		(CASE WHEN UNIX_TIMESTAMP(fpm.commencement_date) IS NULL THEN org_commencement_date
				WHEN UNIX_TIMESTAMP(fpm.commencement_date) < org_commencement_date THEN org_commencement_date
				ELSE UNIX_TIMESTAMP(fpm.commencement_date) END) device_commencement_date,					
						
		(CASE WHEN fpm.commencement_black_meter IS NULL AND mtr.black_meter IS NOT NULL THEN mtr.black_meter
				WHEN mtr.black_meter > fpm.commencement_black_meter THEN (mtr.black_meter - fpm.commencement_black_meter)
				ELSE 0 END)  consumed_blk,

		(CASE WHEN fpm.commencement_color_meter IS NULL AND mtr.color_meter IS NOT NULL THEN mtr.color_meter
				WHEN mtr.color_meter > fpm.commencement_color_meter THEN (mtr.color_meter - fpm.commencement_color_meter)
				ELSE 0 END) consumed_col,				
		
		(CASE WHEN fpm.commencement_black_meter IS NULL AND mtr.black_meter IS NOT NULL THEN mtr.black_meter * fpm.cpc_black
				WHEN mtr.black_meter > fpm.commencement_black_meter THEN (mtr.black_meter - fpm.commencement_black_meter) * fpm.cpc_black
				ELSE 0 END) consumed_blk_cost,			
		
				
		(CASE WHEN fpm.commencement_color_meter IS NULL AND mtr.color_meter IS NOT NULL THEN mtr.color_meter * fpm.cpc_color
				WHEN mtr.color_meter > fpm.commencement_color_meter THEN (mtr.color_meter - fpm.commencement_color_meter) * fpm.cpc_color
				ELSE 0 END) consumed_col_cost,		
		 
		fpm.budgeted_black budgeted_blk, fpm.budgeted_color budgeted_col,
					
		(SELECT SUM(student_pop) spop FROM buildings WHERE organizations_id = param_org) student_pop_tot,
		mtr.created_date, fpm.closeout_date, fpm.cpc_black, fpm.cpc_color, fpm.proposed_serial_number, bldgs.bldg_name
						
		from floorplan_machines as `fpm`
		inner join floorplans as `fp` on `fp`.`fp_id` = `fpm`.`floorplans_id`
		inner join buildings as `bldgs` on `bldgs`.`bldg_id` = `fp`.`buildings_id`	
		
		
		left join (select max(created_date) as dt, serial_number from meters
					where `created_date` >= starting_date and `created_date` <= timeline_date
					and `black_meter` > 0 group by `serial_number`) as `serNumDateMetersMax`
					on `fpm`.`proposed_serial_number` = `serNumDateMetersMax`.`serial_number`

		left join meters as `mtr` on `mtr`.`serial_number` = `serNumDateMetersMax`.`serial_number`
					and `mtr`.`created_date` = `serNumDateMetersMax`.`dt`
					
		
		where `fpm`.`proposed_serial_number` in (select `fm`.`proposed_serial_number` from floorplan_machines as `fm`
														inner join floorplans as `fp` on `fp`.`fp_id` = `fm`.`floorplans_id`
														inner join buildings as `bldgs` on `bldgs`.`bldg_id` = `fp`.`buildings_id`
														where `fm`.`fyer_groups_id`is not null
														and `bldgs`.`organizations_id` = param_org);
				
	
	
	/*When an org has gone through an upgrade, there will be machines that have been traded out. These machines are
            not counted in the regular totals, so we need to get the volume data for these machines from the machine_archive table.
            This will be a total for all buildings in the org that have been traded out. The reason for this ais to make sure the total
            for over/under budget is correct and the budgeted values match those values in the compare report.*/
	
	
	/*Declare cursors to get trade-out data. org_upgrade_cursor ONLY determines IF the org DID have an upgrade. If so, then the next cursor
		trade_out_data_cursor will be opened for data retrieval.*/
	
	 									
	/*Now get the volumes from all machines that were traded. This will be added to the sum of totals from all buildings.*/
	DECLARE trade_out_data_cursor CURSOR FOR SELECT
									SUM(meter_end_read_black - meter_begin_read_black) trade_out_cons_blk,
                                    SUM(meter_end_read_color - meter_begin_read_color) trade_out_cons_col,
                                    SUM((meter_end_read_black - meter_begin_read_black) * cpc_black) trade_out_cons_blk_cost,
                                    SUM((meter_end_read_color - meter_begin_read_color) * cpc_color) trade_out_cons_col_cost
									FROM machine_archive
									WHERE organizations_id = param_org
									AND traded = 'Y'
									AND school_year = param_fiscal_year;
     
	
	/* declare NOT FOUND handler */
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
	
    
	
	/*Set the value for the year in the timeline based on which month of the fiscal year it is.
		July through December will be the param_fiscal_year - 1. Then January through
		June will be the param_fiscal_year.*/
	IF param_month <= 6 THEN SET calendar_year = param_fiscal_year;
	ELSE SET calendar_year = param_fiscal_year - 1;
	END IF;
	
	/*Set the month_name value based on the number of the param_month. This will be inserted into the table for the graph to display on the x axis.*/
	CASE param_month
		WHEN 1 THEN SET month_name = 'January';
		WHEN 2 THEN SET month_name = 'February';
		WHEN 3 THEN SET month_name = 'March';
		WHEN 4 THEN SET month_name = 'April';
		WHEN 5 THEN SET month_name = 'May';
		WHEN 6 THEN SET month_name = 'June';
		WHEN 7 THEN SET month_name = 'July';	
		WHEN 8 THEN SET month_name = 'August';
		WHEN 9 THEN SET month_name = 'September';
		WHEN 10 THEN SET month_name = 'October';
		WHEN 11 THEN SET month_name = 'November';
		WHEN 12 THEN SET month_name = 'December';
		ELSE SET month_name = 'No Month';
	END CASE;
	
	    
    SET current_year = YEAR(CURDATE()) - 2000;
	
    /*NOTE: This variable is not used in the table names yet. Set the yearTab variable, which will be the prefix for the tables that are queried*/	
	
	IF current_year = (param_fiscal_year) AND param_month <= 6 THEN SET yearTab = '';
	ELSEIF current_year + 1 = (param_fiscal_year) AND param_month > 6 THEN SET yearTab = '';
	ELSE SET yearTab = concat('y20', param_fiscal_year, '_');
	END IF;
	
	   
	
	/*The time of the insertions will be Midnight on the last day of every month. Since months have differing
		number of days, we need to get the number of the last day depending on the month.*/
	
	IF param_month = 2 THEN
		SET timeline_date = concat('20', calendar_year, '-', param_month, '-28 23:59:59');
	ELSEIF param_month IN(4, 6, 9, 11)THEN
		SET timeline_date = concat('20', calendar_year, '-', param_month, '-30 23:59:59');
	ELSE
		SET timeline_date = concat('20', calendar_year, '-', param_month, '-31 23:59:59');
	END IF;
	
	
	/*We would normally subtract an interval of 4 hours to get to GMT, BUT for the endingDate we want
		to complete the day. So adding 19 hours, + 59 mins + 59 seconds gets us to 1 second before midnight on the
		last day of the fiscal year.*/	
	SET timelineDate = UNIX_TIMESTAMP(timeline_date - Interval 4 hour);	
	SET endingDate = timelineDate;
	SET endingDateForProjCalcs = endingDate - (20*60*60*24);
	SET starting_date = concat('20', param_fiscal_year - 1, '-07-01 00:00:00');

	/*Subtract an interval of 4 hours to get to GMT*/	
	SET startingDate = UNIX_TIMESTAMP(starting_date - Interval 4 hour);			
	SET FiscalYearEndDateUnix = UNIX_TIMESTAMP(concat('20' , param_fiscal_year, '-06-30 23:59:59') - Interval 4 hour);
	
	SET fiscal_days = CASE WHEN(FiscalYearEndDateUnix - startingDate) <= 0 THEN 0 ELSE (FiscalYearEndDateUnix - startingDate) /60/60/24 END;
	SET notReportingDate = endingDate - (15*60*60*24);	
	
	
	/*If this is any year other than the current fiscal year, the data has been archived into the org_year_specific_data table.*/	
	IF yearTab != '' THEN
		select UNIX_TIMESTAMP(orgs.commencement_date - Interval 4 hour) INTO org_commencement_date_raw
                                from `org_year_specific_data` as `orgs`
                                inner join `organizations` on `organizations`.`org_id` = `orgs`.`org_id`
                                where `orgs`.`school_year` = param_fiscal_year and `orgs`.`org_id` = param_org;
	ELSE
		select UNIX_TIMESTAMP(orgs.commencement_date - Interval 4 hour) INTO org_commencement_date_raw
                                from `organizations` as `orgs`                                
                                where `orgs`.`org_id` = param_org;	
	END IF;
	
	
		
	IF org_commencement_date_raw IS NULL OR org_commencement_date_raw < startingDate THEN
		SET org_commencement_date = startingDate;
	ELSE
		SET org_commencement_date = org_commencement_date_raw;
	END IF;
	
	
	
	IF endingDate - org_commencement_date > 0 THEN
		/* SJH - 8/18/2023 - subtract 20 days from the total for the fiscal year per Pam. */
		SET org_fiscal_days = CASE WHEN ((endingDate - org_commencement_date) /60/60/24) > 345 THEN 345
			ELSE (endingDate - org_commencement_date) /60/60/24 END;
	ELSE
		SET org_fiscal_days = 1;
	END IF;
	
			
	SET org_cm_diff = org_fiscal_days;
	SET cost_increase_multiplier = 1.035;
	
			
	OPEN data_cursor;
	
	getData: LOOP
		
		FETCH data_cursor INTO deviceCommencementDate,
					consumed_black_vol, consumed_color_vol, consumed_black_cost_var, consumed_color_cost_var,
					budgeted_blk_var, budgeted_col_var, student_pop_tot, meter_created_date, closeout_date_var,
					cpc_black_var, cpc_color_var, ser_num, building_name_var;
		
		/*After FETCH is called, the handler will set finished to 1, so if finished = 1 there was no new data fetched.
			The last row is still in the variables, so a dupe would be added if we don't leave the loop. */
		IF finished = 1 THEN  
			LEAVE getData;
		END IF;
		
		
		
			
		/*Subtract an interval of 4 hours to get to our timezone*/
		SET lastMeterDate = UNIX_TIMESTAMP(meter_created_date - Interval 4 hour);	
		
		
		SET last_diff = CASE WHEN lastMeterDate IS NULL THEN 1
							WHEN ((lastMeterDate - deviceCommencementDate) / 60/60/24) <= 0 THEN 1
							ELSE ((lastMeterDate - deviceCommencementDate) / 60/60/24) END;						
								
		SET cm_diff = CASE WHEN ((endingDateForProjCalcs - deviceCommencementDate) /60/60/24) <= 0 THEN 0
						ELSE ((endingDateForProjCalcs - deviceCommencementDate) /60/60/24) END;
						
				
		/*IF org_commencement_date_raw IS NULL OR org_commencement_date_raw <= startingDate THEN
			IF budgeted_blk_var > 0 AND fiscal_days > 0 THEN
				SET budgeted_black_var = budgeted_blk_var;
			ELSE
				SET budgeted_black_var = 0;
			END IF;
		ELSE
			SET budgeted_black_var = budgeted_blk_var / fiscal_days * org_fiscal_days;
		END IF;*/
		
		
		IF org_commencement_date_raw IS NULL OR org_commencement_date_raw <= startingDate THEN
			SET budgeted_black_var = budgeted_blk_var;			
		ELSE
			IF budgeted_blk_var > 0 AND fiscal_days > 0 THEN
				SET budgeted_black_var = budgeted_blk_var / fiscal_days * org_fiscal_days;
			ELSE
				SET budgeted_black_var = 0;
			END IF;
		END IF;		
		
		
		IF org_commencement_date_raw IS NULL OR org_commencement_date_raw <= startingDate THEN
			IF budgeted_col_var > 0 AND fiscal_days > 0 THEN
				SET budgeted_color_var = budgeted_col_var;
			ELSE
				SET budgeted_color_var = 0;
			END IF;
		ELSE
			SET budgeted_color_var = budgeted_col_var / fiscal_days * org_fiscal_days;
		END IF;
						
				
	
		/*SJH - Note - the notReportingDate is just 15 days earlier than the last meter read date.*/
		IF meter_created_date IS NULL THEN
			SET reporting = 'N';
		ELSEIF lastMeterDate < notReportingDate THEN
			SET reporting = 'N';
		ELSE
			SET reporting = 'Y';
		END IF;

		/*Get the device closeout_date for use in setting the projected values for machines no longer in use*/		
		SET closeoutDateUnix = UNIX_TIMESTAMP(closeout_date_var);

		/*If consumed or budgeted values are negative or null, set them = 0. We don't want negative numbers
		that don't make sense, and null values cause errors in calculations. */
		IF consumed_black_vol < 0 THEN
			SET consumed_black_vol = 0;
		END IF;		
		
		IF consumed_color_vol < 0 THEN
			SET consumed_color_vol = 0;
		END IF;
		
		IF budgeted_black_var < 0 || budgeted_black_var is null THEN
			SET budgeted_black_var = 0;
		END IF;
		
		IF budgeted_color_var < 0 || budgeted_color_var is null THEN
			SET budgeted_color_var = 0;		
		END IF;
					
		
		IF cpc_black_var is null THEN
			SET cpc_black_var = 0;		
		END IF;
		
		IF cpc_color_var is null THEN
			SET cpc_color_var = 0;		
		END IF;	
		
		
		/*IF org_commencement_date_raw IS NULL OR org_commencement_date_raw <= startingDate THEN
			IF budgeted_blk_var > 0 AND fiscal_days > 0 THEN
				SET budgeted_black_var = budgeted_blk_var / fiscal_days * org_fiscal_days;
				SET budgeted_black_cost = budgeted_black_var / fiscal_days * org_fiscal_days * cpc_black_var;
			ELSE
				SET budgeted_black_var = 0;
				SET budgeted_black_cost = 0;
			END IF;	
			
		ELSE
			SET budgeted_black_var = budgeted_blk_var;
			SET budgeted_black_cost = budgeted_black_var * cpc_black_var;
			
		END IF;*/
		
		
		
		
		/*Get budgeted costs*/
		IF org_commencement_date_raw IS NULL OR org_commencement_date_raw <= startingDate THEN
			IF budgeted_black_var > 0 AND cpc_black_var > 0 THEN
				SET budgeted_black_cost = budgeted_black_var * cpc_black_var;
			ELSE
				SET budgeted_black_cost = 0;
			END IF;
			
			IF budgeted_color_var > 0 AND cpc_color_var > 0 THEN
				SET budgeted_color_cost = budgeted_color_var * cpc_color_var;
			ELSE
				SET budgeted_color_cost = 0;
			END IF;
		ELSE
			IF budgeted_black_var > 0 AND fiscal_days > 0 AND cpc_black_var > 0 THEN
				SET budgeted_black_cost = budgeted_black_var / fiscal_days * org_fiscal_days * cpc_black_var;
			ELSE
				SET budgeted_black_cost = 0;
			END IF;
			
			
			IF budgeted_color_var > 0 AND fiscal_days > 0 AND cpc_color_var > 0  THEN
				SET budgeted_color_cost = budgeted_color_var / fiscal_days * org_fiscal_days * cpc_color_var;
			ELSE
				SET budgeted_color_cost = 0;
			END IF;
		END IF;
		
		
		
		
					
		/*Begin - Iterate through all of the conditional processing and calculations */				
			
			/*Get the estimated volumes and costs*/
		
			IF cm_diff > 0 /*AND org_cm_diff > 0*/ THEN
				BEGIN
					IF consumed_black_vol > 0 THEN
						SET consumed_black_estimated = (consumed_black_vol / cm_diff * org_cm_diff);
					ELSE
						SET consumed_black_estimated = 0;
					END IF;
					
					IF consumed_black_cost_var > 0 THEN
						SET consumed_black_estimated_cost = (consumed_black_cost_var / cm_diff * org_cm_diff);
					ELSE
						SET consumed_black_estimated_cost = 0;
					END IF;
					
					IF consumed_color_vol > 0 THEN
						SET consumed_color_estimated = (consumed_color_vol / cm_diff * org_cm_diff);
					ELSE
						SET consumed_color_estimated = 0;
					END IF;
					
					IF consumed_color_cost_var > 0 THEN
						SET consumed_color_estimated_cost = (consumed_color_cost_var / cm_diff * org_cm_diff);
					ELSE
						SET consumed_color_estimated_cost = 0;
					END IF;		
					
				END;
			ELSE				
				SET consumed_black_estimated = consumed_black_vol;
				SET consumed_black_estimated_cost = consumed_black_cost_var;
				SET consumed_color_estimated = consumed_color_vol;
				SET consumed_color_estimated_cost = consumed_color_cost_var;
			END IF;
			
			
			
			/*Get projected costs*/
			IF org_commencement_date_raw IS NULL OR org_commencement_date_raw <= startingDate THEN
				IF consumed_black_cost_var > 0 AND last_diff > 0 THEN
					SET projected_black_cost = consumed_black_cost_var / last_diff * 345;
					SET proj_blk_year_next_cost = projected_black_cost * cost_increase_multiplier;
				ELSE
					SET projected_black_cost = 0;
					SET proj_blk_year_next_cost = 0;
				END IF;
				
				IF consumed_color_cost_var > 0 AND last_diff > 0 THEN
					SET projected_color_cost = consumed_color_cost_var / last_diff * 345;
					SET proj_col_year_next_cost = projected_color_cost * cost_increase_multiplier;
				ELSE
					SET projected_color_cost = 0;
					SET proj_col_year_next_cost = 0;
				END IF;
			ELSE
				IF consumed_black_cost_var > 0 AND cm_diff > 0 THEN
					SET projected_black_cost = consumed_black_cost_var / cm_diff * LEAST(org_fiscal_days, 345);
					SET proj_blk_year_next_cost = consumed_black_cost_var / cm_diff * LEAST(org_fiscal_days, 345) * cost_increase_multiplier;
				ELSE
					SET projected_black_cost = 0;
					SET proj_blk_year_next_cost = 0;
				END IF;
				
				
				IF consumed_color_cost_var > 0 AND cm_diff > 0 THEN
					SET projected_color_cost = consumed_color_cost_var / cm_diff * LEAST(org_fiscal_days, 345);
					SET proj_col_year_next_cost = consumed_color_cost_var / cm_diff * LEAST(org_fiscal_days, 345) * cost_increase_multiplier;
				ELSE
					SET projected_color_cost = 0;
					SET proj_col_year_next_cost = 0;
				END IF;
			END IF;
			
								
						
			/*Do further processing for projected values*/
			IF reporting = 'N' && org_fiscal_days > 0 THEN
				IF budgeted_black_var > 0 AND fiscal_days > 0 THEN
					IF proj_blk_year_next < (budgeted_black_var / org_fiscal_days * fiscal_days) THEN BEGIN
						SET projected_black_var = budgeted_black_var;
						SET proj_blk_year_next = projected_black_var;
						SET projected_black_cost = budgeted_black_cost;
						SET proj_blk_year_next_cost = projected_black_cost * cost_increase_multiplier;
						END;
					END IF;				
				END IF;
				
				IF budgeted_color_var > 0 AND org_fiscal_days > 0 AND fiscal_days > 0 THEN
					IF proj_col_year_next < (budgeted_color_var / org_fiscal_days * fiscal_days) THEN BEGIN				
						SET projected_color_var = budgeted_color_var;
						SET proj_col_year_next = projected_color_var;
						SET projected_color_cost = budgeted_color_cost;
						SET proj_col_year_next_cost = projected_color_cost * cost_increase_multiplier;
						END;
						
					END IF;				
				END IF;
			
			END IF;
			
			
			/*If the closeout date for a device has passed then this device is no longer in use.  
				Set the projected amounts to match the consumed amounts because the device will not add to
				print total.  Set the projected next years volume to zero.*/
			IF closeout_date_var IS NOT NULL && closeoutDateUnix < endingDate THEN
				BEGIN					
					SET projected_black_var = consumed_black_estimated;
					SET projected_black_cost = consumed_black_estimated_cost;
					SET projected_color_var = consumed_color_estimated;
					SET projected_color_cost = consumed_color_estimated_cost;
					
					SET proj_blk_year_next = 0;
					SET proj_blk_year_next_cost = 0;
					SET proj_col_year_next = 0;
					SET proj_col_year_next_cost = 0;			
				END;
			END IF;
		/*End - Iterate through all of the conditional processing and calculations */
		 
		
		
		SET consumed_black_tot_cost = (consumed_black_tot_cost + consumed_black_cost_var);
		SET consumed_color_tot_cost = (consumed_color_tot_cost + consumed_color_cost_var);
		SET budgeted_black_tot_cost = (budgeted_black_tot_cost + budgeted_black_cost);
		SET budgeted_color_tot_cost = (budgeted_color_tot_cost + budgeted_color_cost);
		SET projected_black_tot_cost = (projected_black_tot_cost + projected_black_cost);
		SET projected_color_tot_cost = (projected_color_tot_cost + projected_color_cost);		
		SET proj_blk_year_next_tot_cost = (proj_blk_year_next_tot_cost + proj_blk_year_next_cost);
		SET proj_col_year_next_tot_cost = (proj_col_year_next_tot_cost + proj_col_year_next_cost);		
			
	END LOOP getData;
	
	
	
	
	/*Reset the finished variable so it will be free to use for the next cursor. */
	SET finished = 0;	
	
	IF projected_black_tot_cost > 0 AND student_pop_tot > 0 THEN
		SET avg_blk_per_student_var = projected_black_tot_cost / student_pop_tot;
	END IF;
	
	IF projected_color_tot_cost > 0 AND student_pop_tot > 0 THEN
		SET avg_col_per_student_var = projected_color_tot_cost / student_pop_tot;
	END IF;
	
	
	/**********************BEGIN TRADE_OUT CALCULATIONS***************************/
	/*When an org has gone through an upgrade, there will be machines that have been traded out. These machines are
            not counted in the regular totals, so we need to get volumes from the machine_archive table to add to the
			current totals. This will be a total for all buildings in the org that have been traded out. There
            is no requirement to list each machine individually. The reason for this is to make sure the total
            for over/under budget is correct and the budgeted values match those values in the compare report.*/
        
        /*We cannot go by the fyer_year here because it can be = or != the school_year. So just make sure
            that the new_upgrade_date falls within the fiscal year (school_year).*/
                            
        select data_id into org_trade_out_id from org_year_specific_data as orgs
		inner join fyer_groups as fg ON fg.organizations_id = orgs.org_id
		WHERE orgs.new_upgrade_date >= starting_date
		AND orgs.new_upgrade_date <= timeline_date
		AND orgs.school_year = param_fiscal_year
		AND fg.fyer_final = 'Y'
		AND orgs.org_id = param_org;		
		        
        
        /*Variables to hold data for machines that have been traded.*/                
        SET trade_out_cons_blk = 0;
        SET trade_out_cons_col = 0;
        SET trade_out_cons_blk_cost = 0;
        SET trade_out_cons_col_cost = 0;
        SET avg_blk_per_student_trade_out = 0;
        SET avg_col_per_student_trade_out = 0;                            
       
       
       /*If this org has had an upgrade...*/   
        IF org_trade_out_id IS NOT NULL THEN
			OPEN trade_out_data_cursor;
	
				getTradeOutData: LOOP
				
					/*Now get the volumes from all machines that were traded. This will be added to the sum of totals from all buildings.*/
					FETCH trade_out_data_cursor INTO trade_out_cons_blk, trade_out_cons_col, trade_out_cons_blk_cost, trade_out_cons_col_cost;
					
					/*After FETCH is called, the handler will set finished to 1, so if finished = 1 there was no new data fetched.
						The last row is still in the variables, so a dupe would be added if we don't leave the loop. */
					IF finished = 1 THEN  
						LEAVE getTradeOutData;
					END IF;
					

					SET consumed_black_tot_cost = consumed_black_tot_cost + trade_out_cons_blk_cost;
					SET consumed_color_tot_cost = consumed_color_tot_cost + trade_out_cons_col_cost;
					SET budgeted_black_tot_cost = budgeted_black_tot_cost + trade_out_cons_blk_cost;
					SET budgeted_color_tot_cost = budgeted_color_tot_cost + trade_out_cons_col_cost;
					SET projected_black_tot_cost = projected_black_tot_cost + trade_out_cons_blk_cost;
					SET projected_color_tot_cost = projected_color_tot_cost + trade_out_cons_col_cost;
				
				END LOOP getTradeOutData;
			CLOSE trade_out_data_cursor;
        END IF;	
		/**********************END TRADE_OUT CALCULATIONS***************************/
	
	
		
		SET over_under_budget_tot = (projected_black_tot_cost + projected_color_tot_cost) - (budgeted_black_tot_cost + budgeted_color_tot_cost);

		IF over_under_budget_tot IS NULL THEN
			SET over_under_budget_tot = 0;
		END IF;	
					
	CLOSE data_cursor;
	
	
		
	/*Insert the data into the table after all records returned in the resultset have been assigned and any additional processing has been done.*/
	INSERT INTO monthly_over_under_budget (school_year, `month`, organizations_id,
		budgeted_black, consumed_black, projected_black, avg_blk_per_student, next_years_black,
		budgeted_color, consumed_color, projected_color, avg_col_per_student, next_years_color,
		over_under_budget, created_by, modified_by)
		
		VALUES(param_fiscal_year, month_name, param_org,
			budgeted_black_tot_cost, consumed_black_tot_cost, projected_black_tot_cost, avg_blk_per_student_var, proj_blk_year_next_tot_cost,
			budgeted_color_tot_cost, consumed_color_tot_cost, projected_color_tot_cost, avg_col_per_student_var, proj_col_year_next_tot_cost,
			over_under_budget_tot, 1271, 1271); 

	
END //
DELIMITER ;

/*CAll load_monthly_over_under_budget_data(23, 10, 96);

CALL initiate_multi_load_monthly_over_under_budget_data();*/