-- ================================================================================
-- ================================================================================
-- E. Gallas
-- May 2007
--
-- verify that the luminosity value columns contain the 
-- expected calculated value.
--
-- ================================================================================
-- ================================================================================
set serveroutput on;

declare

/* program control */

/* save time by skipping records, set to 1 for no skipping
   Note there a 37 DL/LBN, 74-296 EL/LBN, 7-459 LT/LBN) */
skip_every_n_LBN   INTEGER :=1000;

/* don't report more than 100 differences found, max will be reported at end */
max_diff_count     INTEGER :=100;

/* counters */
count_rows         INTEGER :=0;
count_LBNs         INTEGER :=0;

count_checked_rows INTEGER :=0;
count_checked_LBNs INTEGER :=0;

count_temp         INTEGER :=0;
count_MaxDiff_NIM  INTEGER :=0;
count_MaxDiff_VME  INTEGER :=0;

-- declare variable for PERIOD from getLumPeriod 
PERIOD VARCHAR2(10) := 'blah';

/* declare variables for the new NIM and new VME luminosity values */
thisLBN  DELIVERED_LUMS.LBN%type;

thisLNIM DELIVERED_LUMS.DEL_LUM_NIM%type;
thisLVME DELIVERED_LUMS.DEL_LUM_NIM%type;
thisLNIM0 DELIVERED_LUMS.DEL_LUM_NIM%type;
thisLVME0 DELIVERED_LUMS.DEL_LUM_NIM%type;

/* I need a place to store the new luminosity columns */
newNIM     DELIVERED_LUMS.DEL_LUM_NIM%type;
newVME     DELIVERED_LUMS.DEL_LUM_NIM%type;

/* declare a string to store the SQL */
mySQL VARCHAR2(1000) := '';
myOUT VARCHAR2(1000) := '';

/* keep track of the previous LBN so I can reduce queries for constants */
lastLBN    DELIVERED_LUMS.LBN%type := 0;

/* store the largest differences and the LBN/TICK/EG_NUM when they occur */
MaxDiff_NIM DELIVERED_LUMS.DEL_LUM_NIM%type :=0.0000000000;
MaxDiff_VME DELIVERED_LUMS.DEL_LUM_NIM%type :=0.0000000000;

MaxDiff_NIM_LBN  INTEGER :=0;
MaxDiff_NIM_TICK INTEGER :=0;
MaxDiff_NIM_EG   INTEGER :=0;
MaxDiff_VME_LBN  INTEGER :=0;
MaxDiff_VME_TICK INTEGER :=0;
MaxDiff_VME_EG   INTEGER :=0;

/* corrected and uncorrected column names */
Lum_NIM_uncorr DELIVERED_LUMS.DEL_LUM_NIM%type;
Lum_VME_uncorr DELIVERED_LUMS.DEL_LUM_NIM%type;
Lum_NIM_corr   DELIVERED_LUMS.DEL_LUM_NIM%type;
Lum_VME_corr   DELIVERED_LUMS.DEL_LUM_NIM%type;
-- ================================================================================
/* DELIVERED_LUMS Table  ------------------------------------------------------- */
-- before the final rename, use this cursor
cursor DL_data is
  select LBN,
         TICK_NUM,
         DEL_LUM_NIM Lum_NIM_uncorr,
 	 DEL_LUM_VME Lum_VME_uncorr,
         DEL_LUM_NIM_NEW Lum_NIM_corr,
 	 DEL_LUM_VME_NEW Lum_VME_corr
         from DELIVERED_LUMS;
-- after the final rename, use this cursor
/* 
  cursor DL_data is
  select LBN,
         TICK_NUM,
         DEL_LUM_NIM Lum_NIM_corr,
         DEL_LUM_VME Lum_VME_corr,
         DEL_LUM_NIM_OLD Lum_NIM_uncorr,
 	 DEL_LUM_VME_OLD Lum_VME_uncorr  
         from DELIVERED_LUMS;
*/
-- ================================================================================
begin
  /* Enable DBMS_OUTPUT and set the buffer size. 
	The buffer size can be between 1 and 1,000,000 */
  dbms_output.enable(1000000);

  mySQL := 'SELECT LOCALTIMESTAMP FROM DUAL';
  EXECUTE IMMEDIATE mySQL into myOUT; 
  dbms_output.put_line ('Verification on Delivered_Lums Table '||myOUT);

-- ================================================================================
/* DELIVERED_LUMS Table  ------------------------------------------------------- */
  for thisRow in DL_data loop
    begin

      thisLBN := thisRow.LBN;
      count_rows  := count_rows + 1;

      IF thisLBN != lastLBN THEN
        count_LBNs  := count_LBNs + 1;
        count_temp  := count_temp + 1;

	IF skip_every_n_LBN = 1 THEN
	  count_temp := 1 ;
        else 
	  if count_temp > skip_every_n_LBN THEN
	    count_temp := 0 ;
          END IF;
        END IF;

	IF count_temp = 1 THEN
          count_checked_LBNs := count_checked_LBNs + 1;
          PERIOD := getLumPeriod(thisLBN) ;
	  mySQL := 'SELECT DEL_LUM_NIM,DEL_LUM_VME 
                    from DELIVERED_LUMS 
                    WHERE LBN='||thisLBN||' AND TICK_NUM=0';
          EXECUTE IMMEDIATE mySQL INTO thisLNIM0,thisLVME0;
        END IF;
      END IF;

      IF count_temp = 1 THEN
        count_checked_rows := count_checked_rows + 1;

--          PERIOD := getLumPeriod(thisLBN) ;
--	  mySQL := 'SELECT DEL_LUM_NIM,DEL_LUM_VME 
--                    from DELIVERED_LUMS 
--                    WHERE LBN='||thisLBN||' AND TICK_NUM=0';
--          EXECUTE IMMEDIATE mySQL INTO thisLNIM0,thisLVME0;

        thisLNIM := thisRow.Lum_NIM_uncorr;
        newNIM   := getCorrectedLum(thisLNIM,thisLNIM0,PERIOD);
        thisLVME := thisRow.Lum_VME_uncorr;
        newVME   := getCorrectedLum(thisLVME,thisLVME0,PERIOD);

        IF abs(thisRow.Lum_NIM_corr - newNIM) > abs(MaxDiff_NIM) THEN
          count_MaxDiff_NIM := count_MaxDiff_NIM + 1;
          MaxDiff_NIM := thisRow.Lum_NIM_corr - newNIM ;
	  MaxDiff_NIM_LBN  := thisLBN ;
          MaxDiff_NIM_TICK := thisRow.TICK_NUM ;
	  IF count_MaxDiff_NIM < max_diff_count THEN
            dbms_output.put_line ('New NIM Max Diff PERIOD/LBN/TICK/Diff = '||
                                   PERIOD||' / '||
                                   thisLBN||' / '||
                                   thisRow.TICK_NUM||' / '||
                                   MaxDiff_NIM);
          END IF;
        END IF;
        IF abs(thisRow.Lum_VME_corr - newVME) > abs(MaxDiff_VME) THEN
          count_MaxDiff_VME := count_MaxDiff_VME + 1;
          MaxDiff_VME := thisRow.Lum_VME_corr - newVME ;
	  MaxDiff_VME_LBN  := thisLBN ;
          MaxDiff_VME_TICK := thisRow.TICK_NUM ;
	  IF count_MaxDiff_VME < max_diff_count THEN
            dbms_output.put_line ('New VME Max Diff PERIOD/LBN/TICK/Diff = '||
                                   PERIOD||' / '||
                                   thisLBN||' / '||
                                   thisRow.TICK_NUM||' / '||
                                   MaxDiff_VME);
          END IF;
        END IF;
      END IF;

-- print the results for the first LBN
      IF count_temp = 1 and count_checked_rows <= 2 THEN
        dbms_output.put_line ('-------------------------------                  ');
        dbms_output.put_line ('First 2 rows -- PERIOD/LBN/TICK/Diff = '||
                                 PERIOD||' / '||
                                 thisLBN||' / '||
                                 thisRow.TICK_NUM||' / '||
                                 to_char(MaxDiff_NIM));
        dbms_output.put_line ('LBN  = '||to_char(thisLBN));
        dbms_output.put_line ('TICK = '||to_char(thisRow.TICK_NUM));
        dbms_output.put_line ('thisLNIM0 = '||to_char(thisLNIM0));
        dbms_output.put_line ('thisLNIM  = '||to_char(thisLNIM));
        dbms_output.put_line ('thisLVME0 = '||to_char(thisLVME0));
        dbms_output.put_line ('thisLVME  = '||to_char(thisLVME));

        dbms_output.put_line ('col NIM/VME = '||to_char(thisRow.Lum_NIM_corr)||' / '||
                                                to_char(thisRow.Lum_VME_corr));
        dbms_output.put_line ('cal NIM/VME = '||to_char(newNIM)||' / '||to_char(newVME));
        dbms_output.put_line ('dif NIM/VME = '||to_char(thisRow.Lum_NIM_corr - newNIM )||' / '||
                                                to_char(thisRow.Lum_VME_corr - newVME));
      END IF;

      lastLBN := thisLBN;

    EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line ('Exception ---------------------------------------');
  	dbms_output.put_line ('Error: NO DATA FOUND for LBN '||to_char(thisLBN));
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);
      WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line ('Exception ---------------------------------------');
        dbms_output.put_line ('Error: TOO MANY ROWS for LBN '||to_char(thisLBN));
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);
      WHEN OTHERS THEN
        dbms_output.put_line ('Exception ---------------------------------------');
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);

    END; 	/* end of begin enclosing loop over data */

  END LOOP; 	/* end of loop over data */

-- print summary
  dbms_output.put_line ('Summary -------------------------------------------------------');
  dbms_output.put_line ('Verification every '||to_char(skip_every_n_LBN)||' LBNs.');

  dbms_output.put_line ('Total rows = '||to_char(count_rows));
  dbms_output.put_line ('Total LBNs = '||to_char(count_LBNs));
  dbms_output.put_line ('Total checked rows = '||to_char(count_checked_rows));
  dbms_output.put_line ('Total checked LBNs = '||to_char(count_checked_LBNs));
  dbms_output.put_line ('Max Diff NIM changed '||to_char(count_MaxDiff_NIM)||' times.');
  dbms_output.put_line ('Max Diff NIM on LBN/TICK  = '||to_char(MaxDiff_NIM)||' / '||to_char(MaxDiff_NIM_LBN)||' / '||to_char(MaxDiff_NIM_TICK));
  dbms_output.put_line ('Max Diff VME changed '||to_char(count_MaxDiff_VME)||' times.');
  dbms_output.put_line ('Max Diff VME on LBN/TICK  = '||to_char(MaxDiff_VME)||' / '||to_char(MaxDiff_VME_LBN)||' / '||to_char(MaxDiff_VME_TICK));


-- ================================================================================
-- ================================================================================

  mySQL := 'SELECT LOCALTIMESTAMP FROM DUAL';
  EXECUTE IMMEDIATE mySQL into myOUT; 
  dbms_output.put_line ('End of program execution '||myOUT);

EXCEPTION 
  WHEN OTHERS THEN
  dbms_output.put_line ('Last exception ----------------------------------------');
  dbms_output.put_line ('ERROR '||sqlerrm);
  dbms_output.put_line ('SQL CODE : '||mySQL);
  RAISE_APPLICATION_ERROR(-20000,'RAISE_APPLICATION_ERROR');

/*  end of first begin loop over all tables; */
END;
/
