DELIMITER //

CREATE OR REPLACE PROCEDURE compareMeterReads (IN audit_date datetime)

BEGIN

      	/*Declare the variables*/
      	DECLARE first_date_val DATETIME;
      	DECLARE ser_num_val VARCHAR(25);		
        DECLARE last_date_val DATETIME;
		
		DECLARE first_black_meter INT;
		DECLARE first_color_meter INT;
		DECLARE last_black_meter INT;
		DECLARE last_color_meter INT;
		
		/*Days between the 2 dates being compared*/
		DECLARE total_days_between_dates INT; 
        
        DECLARE black_diff INT;
        DECLARE color_diff INT;
		
		DECLARE black_avg_to_date INT;
        DECLARE color_avg_to_date INT;
        
        
        /*When this = 1, the fetch will be done*/
        DECLARE exit_results_fetch INT DEFAULT 0;
        
      
      	/*Declare the cursor that will get the earliest created_date and serial_number
        for each serial_number.*/
		DECLARE results_cursor CURSOR FOR
       
      	SELECT MIN(created_date) first_date, serial_number
        FROM `y2019_meters`
        WHERE created_date > audit_date AND serial_number != ''
        GROUP BY serial_number;
        
        /*Declare the cursor that will get the earliest created_date, that follows
        the first_date from the results_cursor, for each serial_number.*/
        DECLARE last_results_cursor CURSOR FOR
            SELECT MIN(created_date) last_date
            FROM `y2019_meters`
            WHERE created_date > first_date_val AND serial_number = ser_num_val
            GROUP BY serial_number;
       
    
		/*Declare NOT FOUND handler*/
		DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET exit_results_fetch = 1;      
               
        
        /*Open the first cursor*/
        OPEN results_cursor;        
        
            getData: LOOP
 
                FETCH results_cursor INTO first_date_val, ser_num_val;            


                /*Open the cursor that will return a last_date value for the current serial_number.*/
                OPEN last_results_cursor;

                    /*Get the results and assign the value to last_date_val*/
                    FETCH last_results_cursor INTO last_date_val;

                /*Close the cursor*/
                CLOSE last_results_cursor; 
            
                BEGIN

                    DECLARE black_meter_val INT;
                    DECLARE color_meter_val INT;
                    DECLARE meter_read_order VARCHAR(10);
					DECLARE black_avg_between_dates INT;
					DECLARE color_avg_between_dates INT;
					

                    /*When this = 1, the fetch will be done*/ 
                    DECLARE exit_volumes_fetch INT DEFAULT 0;


                    DECLARE volumes_cursor CURSOR FOR

                    SELECT black_meter, color_meter, 'first_read' read_order
                    FROM `y2019_meters`
                    WHERE created_date = first_date_val
                    AND serial_number = ser_num_val

                    UNION

                    SELECT black_meter, color_meter, 'last_read' read_order
                    FROM `y2019_meters`
                    WHERE created_date = last_date_val
                    AND serial_number = ser_num_val;


                    /*Declare NOT FOUND handler*/
                    DECLARE CONTINUE HANDLER 
                    FOR NOT FOUND SET exit_volumes_fetch = 1;

            
            
                   /*Open the cursor that will return the black and color meter values for first and last meter reads
                    for the current serial_number*/
                    OPEN volumes_cursor;

                        getVolumes: LOOP

                            /*Get the results and assign the values to black_meter_val, color_meter_val, and meter_read_order*/        	           
                            FETCH volumes_cursor INTO black_meter_val, color_meter_val, meter_read_order;

                            IF (meter_read_order = "first_read") THEN
                                SET first_black_meter = black_meter_val;
                                SET first_color_meter = color_meter_val;                                

                            ELSEIF (meter_read_order = "last_read") THEN
                                SET last_black_meter = black_meter_val;
                                SET last_color_meter = color_meter_val;                                
								
							ELSE
								SET exit_results_fetch = 1;
                            END IF;
                            
                            

                            IF exit_volumes_fetch = 1 THEN
                                LEAVE getVolumes;
                            END IF;                            

                        END LOOP getVolumes; 


                    /*Close the cursor*/
                    CLOSE volumes_cursor;
					
					
					SET black_diff = (last_black_meter - first_black_meter);                            
					SET color_diff = (last_color_meter - first_color_meter);
					
					/*Get total amount of days between the dates being compared. This will be divided
						into the total copies for the same time period, to get the total average copies printed per day.*/
					SELECT DATEDIFF(last_date_val, first_date_val) INTO total_days_between_dates;
					
					SET black_avg_between_dates = (black_diff/total_days_between_dates);
					SET color_avg_between_dates = (color_diff/total_days_between_dates);
					
					
					
					SELECT getAvgToDate(first_date_val, 'black', ser_num_val) INTO black_avg_to_date;
					SELECT getAvgToDate(first_date_val, 'color', ser_num_val) INTO color_avg_to_date;
					
							  
					
					/*Insert all values, along with the serial_number and corresponding date_timestamps, into the table*/
					SET @aSql = 'INSERT INTO meter_read_comparisons (serial_number, first_date, last_date, tot_days_between_dates,
								blk_daily_avg_to_date, col_daily_avg_to_date, total_black_copies, total_color_copies,
								black_average_between_dates, color_average_between_dates)  VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
					
					PREPARE stmt FROM @aSql;

					if(last_date_val > first_date_val) THEN
					
					EXECUTE stmt USING ser_num_val, first_date_val, last_date_val, total_days_between_dates, black_avg_to_date,
									color_avg_to_date, black_diff, color_diff, black_avg_between_dates, color_avg_between_dates;
					END IF;					
					
					DEALLOCATE PREPARE stmt;					
					
               END;  
            
            
            
          
          	IF exit_results_fetch = 1 THEN
           		LEAVE getData;
           	END IF; 
          
		END LOOP getData; 
            
        
        /*Close the cursor*/
        CLOSE results_cursor;	   
		
         
       END //

DELIMITER ;