DELIMITER //

CREATE DEFINER=`spcsta5`@`cpe-67-255-194-115.maine.res.rr.com` FUNCTION getAvgToDate(audit_date DATETIME, blk_col VARCHAR(5), ser_num VARCHAR(25))
RETURNS INT
NOT DETERMINISTIC

BEGIN

	DECLARE fiscal_year VARCHAR(4);
    DECLARE actual_year_begin VARCHAR(25);
    DECLARE first_yearly_meter_read_date VARCHAR(25);
    DECLARE total_printing_days INT;
	
	DECLARE first_meter_read_vol INT;
	DECLARE last_meter_read_vol INT;
	DECLARE cur_yearly_avg INT;
	
	
	 
    
    /*Declare the cursor that will get the earliest meter read date for this serial number that is
    	in the same fiscal year as the audit_date. The total days, used to CALCULATE THE AVERAGE
        copies/day, will be the difference between the first actual meter read for this
        serial number and the audit_date. 1 row will be returned.*/
        
    DECLARE getFirstMeterReadDate CURSOR FOR
    
    SELECT MIN(date_timestamp) first_meter_read_date    
    FROM meter
    WHERE serial_number = ser_num
    AND date_timestamp BETWEEN actual_year_begin AND audit_date
    GROUP BY serial_number;
	

	/*Declare the cursors for black_meter reads. Only 1 (black or color) will be used per call to this function.*/
	DECLARE getFirstMeterReadVolBlack CURSOR FOR	
		
	SELECT black_meter    
	FROM meter
	WHERE serial_number = ser_num
	AND date_timestamp = first_yearly_meter_read_date
	GROUP BY serial_number;
	
	DECLARE getLastMeterReadVolBlack CURSOR FOR
	
	SELECT black_meter    
	FROM meter
	WHERE serial_number = ser_num
	AND date_timestamp = audit_date
	GROUP BY serial_number;
	

	
	/*Declare the cursors for color_meter reads. Only 1 (black or color) will be used per call to this function.*/
	DECLARE getFirstMeterReadVolColor CURSOR FOR	
		
	SELECT color_meter    
	FROM meter
	WHERE serial_number = ser_num
	AND date_timestamp = first_yearly_meter_read_date
	GROUP BY serial_number;
	
	DECLARE getLastMeterReadVolColor CURSOR FOR
	
	SELECT color_meter    
	FROM meter
	WHERE serial_number = ser_num
	AND date_timestamp = audit_date
	GROUP BY serial_number;	
	
	
    
    
    /*Concat the date that the fiscal year begins. Use the same year
    	as the audit_date and the default fiscal date .*/
	SELECT YEAR(audit_date) INTO fiscal_year;
	
	
	IF (MONTH(audit_date) < 7) THEN
		SET fiscal_year = (fiscal_year - 1);
	END IF;
	

	SET actual_year_begin = CONCAT(fiscal_year, '-07-01 00:00:00');

	OPEN getFirstMeterReadDate;
     
		FETCH getFirstMeterReadDate INTO first_yearly_meter_read_date;

	CLOSE getFirstMeterReadDate;
    
    
    /*Get total_printing_days. This will be divided into the total copies for the
		same time period, to get the average copies printed per day.*/
	SELECT DATEDIFF(audit_date, first_yearly_meter_read_date) INTO total_printing_days;
	
	
	
	IF (blk_col = "black") THEN
		OPEN getFirstMeterReadVolBlack;
		 
			FETCH getFirstMeterReadVolBlack INTO first_meter_read_vol;

		CLOSE getFirstMeterReadVolBlack;
		
		
		OPEN getLastMeterReadVolBlack;
		 
			FETCH getLastMeterReadVolBlack INTO last_meter_read_vol;

		CLOSE getLastMeterReadVolBlack;
		
	ELSEIF (blk_col = "color") THEN
		OPEN getFirstMeterReadVolColor;
		 
			FETCH getFirstMeterReadVolColor INTO first_meter_read_vol;

		CLOSE getFirstMeterReadVolColor;
		
		
		OPEN getLastMeterReadVolColor;
		 
			FETCH getLastMeterReadVolColor INTO last_meter_read_vol;

		CLOSE getLastMeterReadVolColor;
	
	END IF;
	
	
	SET cur_yearly_avg = ROUND((last_meter_read_vol - first_meter_read_vol) / total_printing_days); 
     
	RETURN cur_yearly_avg;

END //

DELIMITER ;