DELIMITER //

CREATE OR REPLACE PROCEDURE compareMeterReads (IN audit_date datetime)

BEGIN

		/*This proc can be executed as a standalone proc, or can be called
			by compareAllMeterReads. It will get the very next meter read,
			following the audit_date, for each machine (serial_number)
			The proc will gather data that will be inserted into the
			meter_read_comparisons table.
			
			The data will include:
			average usage per day for the fiscal year up to the first meter read,
			average usage per day between the 2 dates being compared,
			total black copies between the 2 dates being compared,
			total color copies between the 2 dates being compared,
			a flag to signal whether or not the data indicates that further audit (evaluation) is needeed.
			
			The criteria that determines whether or not the data indicates a need for
			further evaluation, can be modified. Criteria condition is on lines 188-192 (approx)*/

      	/*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 `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 `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;      
               
        
		/*Clear the data from the previous execution from the table. This is only good if executed ONLY 1 day at a time. 
		TRUNCATE meter_read_comparisons;*/
		
        /*Open the first cursor*/
        OPEN results_cursor;        
        
            getData: LOOP
 
                FETCH results_cursor INTO first_date_val, ser_num_val;            

				/*Clear the last_date_val. If a machine has no NEXT read date to follow the first_date_val, THEN
					the last_date_val would otherwise carry over from the last machine that DID have a Next read date
					and it could cause erroneous volume amounts.*/
				SET last_date_val = NULL;
                
				/*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;
					DECLARE needs_audit VARCHAR(1);
					DECLARE dupe_serial_number VARCHAR(25);
					

                    /*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 `meters`
                    WHERE created_date = first_date_val
                    AND serial_number = ser_num_val

                    UNION

                    SELECT black_meter, color_meter, 'last_read' read_order
                    FROM `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;
					
					
					/*The amount of copies printed between the 2 dates being compared.*/
					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;
					
					/*The average copies per day between the 2 dates being compared.*/
					SET black_avg_between_dates = (black_diff/total_days_between_dates);
					SET color_avg_between_dates = (color_diff/total_days_between_dates);
					
					
					/*The average copies per day between the first meter read of the fiscal year
						for this serial number and the first date of the 2 being compared.*/
					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;
					
					/*IF ((black_avg_between_dates > (black_avg_to_date * 1.5)) || (color_avg_between_dates > (color_avg_to_date * 1.5))
						|| ((black_avg_between_dates * 1.5) < black_avg_to_date) || ((color_avg_between_dates * 1.5) < color_avg_to_date)) THEN
						SET needs_audit = "Y";
					
					END IF;*/
					
					/*Criteria to determine whether or not further evaluation is needed.*/
					IF ((black_avg_between_dates > (black_avg_to_date + 1000)) || (color_avg_between_dates > (color_avg_to_date + 1000))
						|| ((black_avg_between_dates + 1000) < black_avg_to_date) || ((color_avg_between_dates + 1000) < color_avg_to_date)) THEN
						SET needs_audit = "Y";					
					END IF;
					
					
					/*Check to see if there is already a record in the table for this serial number with this date. If one exists,
						then do not insert another one. If there are a few days between reads, then the next date will be inserted
						for each of the missing dates, which is duplicating the data. For instance, if a read is 12-21-2018 and the
						next read for that machine is 12-26-2018, the date of 12-26-2018 will be inserted for 12-22, 12-23,  12-24,
						12-25, snd 12-26. To be able to have this called for a date range, this cannot be helped because it would
						mean running through the table an extra time in the compareAllMeterReads procedure that calls this one. This
						could be rewritten to move the dynamic date functionality to the calling proc, but no time to do that now.*/
					SELECT serial_number INTO dupe_serial_number FROM meter_read_comparisons WHERE serial_number = ser_num_val AND first_date = first_date_val;


					
					
					/*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,
								read_audit_needed, blk_daily_avg_to_date, black_average_between_dates, col_daily_avg_to_date,
								color_average_between_dates, total_black_copies, total_color_copies
								)  VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
					
					PREPARE stmt FROM @aSql;

					/*Check to see if there is a read beyond the first date. If there is no last_date_val, then there
						is no volume data to insert.*/					
					if(last_date_val IS NOT NULL && dupe_serial_number IS NULL) THEN
					
						EXECUTE stmt USING ser_num_val, first_date_val, last_date_val, total_days_between_dates, needs_audit, black_avg_to_date,
									black_avg_between_dates, color_avg_to_date, color_avg_between_dates, black_diff, color_diff;									
						
					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 ;