-- ================================================================================
-- ================================================================================
-- E. Gallas
-- June 6, 2006
--
-- write update statments to load the new columns with the new luminosity
-- based on existing values and the given functions.
--
-- This file: 060606_UpdateLum.sql
--
-- Table LumPeriodTable1 
--   defined below contains the Period names and their begin/end LBNs
-- 
-- Function getCorrectedLum
--   in file 060704_function.sql is the function that 
--   output:  the corrected luminosity given 
--   input:   luminosity, tick0Luminosity, LBN
-- ================================================================================
-- ================================================================================
-- Define a table that stores the 6 distinct data taking periods 
--   and their lbn ranges (LO and HI)

DROP TABLE LumPeriodTable1;
CREATE TABLE LumPeriodTable1 (PERIOD VARCHAR2(10),
                              LBN_LO INTEGER,
                              LBN_HI INTEGER);
DELETE FROM LumPeriodTable1;

-- ================================================================================
-- I decided on the following LBN ranges based on the Run ranges from Greg/Brendan
-- These LBN start/end boundaries include all LBNs for the Run at the boundary 
-- plus some LBNs before/after, respectively.
-- The LBN ranges are chosen such that there are no D0 LBNs outside  these periods.

insert into LumPeriodTable1 values 
	('Run 2A:A', 1129853, 3736259);
insert into LumPeriodTable1 values 
	('Run 2A:B', 3736260, 3757957);
insert into LumPeriodTable1 values 
	('Run 2A:C', 3757958, 3921401);
insert into LumPeriodTable1 values 
	('Run 2A:D', 3921405, 4317937);
insert into LumPeriodTable1 values 
	('Run 2A:E', 4317939, 4715213);
insert into LumPeriodTable1 values 
	('Run 2B',   4715214, 9999999);

COMMIT;

-- ================================================================================
set serveroutput on;

declare

/* create a DEBUG flag for turning on/off print statements */
DEBUG VARCHAR2(3) := 'on';

/* counter_number == number of updates since the last commit;
/* commit every commit_count */
counter              INTEGER :=0;
commit_count         INTEGER := 99999;
count_period_changes INTEGER :=0;

/*  these are to double check the counts at the end for DL, EL, LT tables */
total_DL_count INTEGER :=0;
total_EL_count INTEGER :=0;
total_LT_count INTEGER :=0;

/* declare variables to retrieve INTO from the LumPeriodTable1 ------------- */
PERIOD LumPeriodTable1.PERIOD%type;
LBN_LO LumPeriodTable1.LBN_LO%type;
LBN_HI LumPeriodTable1.LBN_HI%type;

/* declare variables for the new NIM and new VME luminosity values */
thisLBN  DELIVERED_LUMS.LBN%type;
thisLum  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;

newNIM0    DELIVERED_LUMS.DEL_LUM_NIM%type;

new_L1NIM  DELIVERED_LUMS.DEL_LUM_NIM%type;
new_L1VME  DELIVERED_LUMS.DEL_LUM_NIM%type;
new_RECNIM DELIVERED_LUMS.DEL_LUM_NIM%type;
new_RECVME 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 LumPeriodTable1.LBN_LO%type    := 0;
lastPeriod LumPeriodTable1.PERIOD%type := 'blah';

-- ================================================================================
/* DELIVERED_LUMS Table  ------------------------------------------------------- */
cursor DL_data is
  select LBN,
         TICK_NUM,
         DEL_LUM_NIM,
 	 DEL_LUM_VME 
         from DELIVERED_LUMS
	where DEL_LUM_NIM_NEW is null;
--  order by LBN,TICK_NUM;
/* EXPOSED_LUMS Table  ------------------------------------------------------- */
cursor EL_data is
  select LBN,
	 EG_NUM,
         TICK_NUM,
         EXP_LUM_NIM,
 	 EXP_LUM_VME 
         from EXPOSED_LUMS
	where exp_lum_nim_new is null;
--  order by LBN,EG_NUM,TICK_NUM;
/* LBN_TRIGGERS Table  ------------------------------------------------------- */
cursor LT_data is
  select LBN,
 	 LT_ID,
         EG_NUM,
         L3_BIT_NAME,
	 L1_LUM_NIM,
 	 L1_LUM_VME,
	 REC_LUM_NIM,
 	 REC_LUM_VME 
         from LBN_TRIGGERS
	where L1_LUM_NIM_NEW is null;
--  order by LBN,LT_ID;

-- ================================================================================
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 ('Working on Delivered_Lums Table '||myOUT);

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

      thisLBN := thisRow.LBN;

-- ================================================================================
-- count huge jumps in LBN
      IF lastLBN-thisLBN > 1000. OR lastLBN-thisLBN < -1000. then
        count_period_changes  := count_period_changes + 1;
        IF count_period_changes<10 THEN
          dbms_output.put_line ('DL: -------------------------------------------');
	  dbms_output.put_line ('DL: New Period !!! '||PERIOD||
                       '  ('||LBN_LO||'-'||LBN_HI||') for LBN '||to_char(thisLBN));
        END IF;
      END IF;
      lastLBN := thisLBN;
-- ================================================================================
-- get the tick0 luminosity
      mySQL := 'SELECT DEL_LUM_NIM,DEL_LUM_VME from DELIVERED_LUMS 
                WHERE LBN='||thisLBN||' AND TICK_NUM=0';
      EXECUTE IMMEDIATE mySQL INTO thisLNIM0,thisLVME0;

      /* Calculate new DEL_LUM_NIM luminosity ---------------------------------- */
      thisLum := thisRow.DEL_LUM_NIM;
      newNIM := getCorrectedLum(thisLum,thisLNIM0,thisLBN);

      /* Calculate new DEL_LUM_VME luminosity ---------------------------------- */
      thisLum := thisRow.DEL_LUM_VME;
      newVME := getCorrectedLum(thisLum,thisLVME0,thisLBN);

      /* Update the table adding the new values -------------------------------- */
      UPDATE DELIVERED_LUMS 
  	     set DEL_LUM_NIM_NEW=newNIM,
                 DEL_LUM_VME_NEW=newVME 
             where LBN=thisLBN and
                 TICK_NUM=thisRow.TICK_NUM;

      counter        := counter + 1;
      total_DL_count := total_DL_count + 1;

      /*  if counter > commit_count then commit */
      if counter>commit_count then
        dbms_output.put_line ('Commit; DL count is now '||to_char(total_DL_count));
        commit;
        counter:=0;
      end if;

    EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line ('DL exception ------------------------------------');
  	dbms_output.put_line ('Error: LBN not found in known LBN periods !  '||
                               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 ('DL exception ------------------------------------');
        dbms_output.put_line ('Error: LBN found in more than one PERIOD  !  '||
                               to_char(thisLBN));
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);
      WHEN OTHERS THEN
        dbms_output.put_line ('DL exception ------------------------------------');
        dbms_output.put_line ('ERROR '||sqlerrm);
        dbms_output.put_line ('SQL CODE : '||mySQL);

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

  end loop; 	/* end of loop over DL data */

  /*  final commit for DL */
  dbms_output.put_line ('Final DL Commit.');
  commit;

  /*  print total DL */
  dbms_output.put_line ('DL: ---------------------------------------------------');
  dbms_output.put_line ('Total DL updates = '||to_char(total_DL_count));
  dbms_output.put_line ('DL Period changes = '||to_char(count_period_changes));

-- ================================================================================
  mySQL := 'SELECT LOCALTIMESTAMP FROM DUAL';
  EXECUTE IMMEDIATE mySQL into myOUT; 
  dbms_output.put_line ('Working on EXPOSED_Lums Table '||myOUT);
-- ================================================================================
  counter :=0;
  count_period_changes :=0;
-- ================================================================================
/* EXPOSED_LUMS Table  ------------------------------------------------------- */
  for thisRow in EL_data loop
    begin

      thisLBN := thisRow.LBN;

-- ================================================================================
-- count huge jumps in LBN
      IF lastLBN-thisLBN > 1000. OR lastLBN-thisLBN < -1000. then
        count_period_changes  := count_period_changes + 1;
        IF count_period_changes<10 THEN
          dbms_output.put_line ('EL: -------------------------------------------');
	  dbms_output.put_line ('EL: New Period !!! '||PERIOD||
                       '  ('||LBN_LO||'-'||LBN_HI||') for LBN '||to_char(thisLBN));
        END IF;
      END IF;
      lastLBN := thisLBN;

-- ================================================================================
-- get the tick0 luminosity
      mySQL := 'SELECT EXP_LUM_NIM,EXP_LUM_VME from EXPOSED_LUMS 
                WHERE LBN='||thisLBN||' AND TICK_NUM=0'||
	      ' AND EG_NUM='||thisRow.EG_NUM;

      EXECUTE IMMEDIATE mySQL INTO thisLNIM0,thisLVME0;

      /* Calculate new EXP_LUM_NIM luminosity ---------------------------------- */
      thisLum := thisRow.EXP_LUM_NIM;
      newNIM := getCorrectedLum(thisLum,thisLNIM0,thisLBN);

      /* Calculate new EXP_LUM_VME luminosity ---------------------------------- */
      thisLum := thisRow.EXP_LUM_VME;
      newVME := getCorrectedLum(thisLum,thisLVME0,thisLBN);

      /* Update the table adding the new values -------------------------------- */
      UPDATE EXPOSED_LUMS 
  	     set EXP_LUM_NIM_NEW=newNIM,
                 EXP_LUM_VME_NEW=newVME 
             where LBN=thisLBN and
	 	 EG_NUM=thisRow.EG_NUM and 
                 TICK_NUM=thisRow.TICK_NUM;

      counter        := counter + 1;
      total_EL_count := total_EL_count + 1;

      /*  if counter > commit_count then commit */
      if counter>commit_count then
        dbms_output.put_line ('Commit; EL count is now '||to_char(total_EL_count));
        commit;
        counter:=0;
      end if;

    EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line ('EL exception ------------------------------------');
  	dbms_output.put_line ('Error: LBN not found in known LBN periods !  '||
                               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 ('EL exception ------------------------------------');
        dbms_output.put_line ('Error: LBN found in more than one PERIOD  !  '||
                               to_char(thisLBN));
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);
      WHEN OTHERS THEN
        dbms_output.put_line ('EL exception ------------------------------------');
        dbms_output.put_line ('ERROR '||sqlerrm);
        dbms_output.put_line ('SQL CODE : '||mySQL);

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

  end loop; 	/* end of loop over EL data */

  /*  final commit for EL */
  dbms_output.put_line ('Final EL Commit.');
  commit;

  /*  print total EL */
  dbms_output.put_line ('EL: ---------------------------------------------------');
  dbms_output.put_line ('Total EL updates = '||to_char(total_EL_count));
  dbms_output.put_line ('EL Period changes = '||to_char(count_period_changes));

-- ================================================================================
counter :=0;
count_period_changes :=0;
mySQL := 'SELECT LOCALTIMESTAMP FROM DUAL';
EXECUTE IMMEDIATE mySQL into myOUT; 
dbms_output.put_line ('Working on LBN_TRIGGERS Table '||myOUT);
-- ================================================================================
/* LBN_TRIGGERS Table  ------------------------------------------------------- */
  for thisRow in LT_data loop
    begin

      thisLBN := thisRow.LBN;

-- ================================================================================
-- count huge jumps in LBN
      IF lastLBN-thisLBN > 1000. OR lastLBN-thisLBN < -1000. then
        count_period_changes  := count_period_changes + 1;
        IF count_period_changes<10 THEN
          dbms_output.put_line ('EL: -------------------------------------------');
	  dbms_output.put_line ('EL: New Period !!! '||PERIOD||
                       '  ('||LBN_LO||'-'||LBN_HI||') for LBN '||to_char(thisLBN));
        END IF;
      END IF;
      lastLBN := thisLBN;

-- ================================================================================
      /* Calculate new L1_LUM_NIM luminosity ----------------------------------- */
      thisLUM   := thisRow.L1_LUM_NIM;
      new_L1NIM := getCorrectedLum(thisLum,thisLum,thisLBN);

      /* Calculate new L1_LUM_VME luminosity ----------------------------------- */
      thisLUM   := thisRow.L1_LUM_VME;
      new_L1VME := getCorrectedLum(thisLum,thisLum,thisLBN);

      /* Calculate new REC_LUM_NIM luminosity ----------------------------------- */
      thisLUM    := thisRow.REC_LUM_NIM;
      new_RECNIM := getCorrectedLum(thisLum,thisLum,thisLBN);

      /* Calculate new REC_LUM_VME luminosity ----------------------------------- */
      thisLUM    := thisRow.REC_LUM_VME;
      new_RECVME := getCorrectedLum(thisLum,thisLum,thisLBN);

      /* Update the table adding the new values -------------------------------- */
      UPDATE LBN_TRIGGERS 
  	     set L1_LUM_NIM_NEW=new_L1NIM,
                 L1_LUM_VME_NEW=new_L1VME, 
                 REC_LUM_NIM_NEW=new_RECNIM,
                 REC_LUM_VME_NEW=new_RECVME 
             where LT_ID=thisRow.LT_ID;

      counter        := counter + 1;
      total_LT_count := total_LT_count + 1;

      /*  if counter > commit_count then commit */
      if counter>commit_count then
        dbms_output.put_line ('Commit; LT count is now '||to_char(total_LT_count));
        commit;
        counter:=0;
      end if;

    EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line ('LT exception ------------------------------------');
  	dbms_output.put_line ('Error: LBN not found in known LBN periods !  '||
                               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 ('LT exception ------------------------------------');
        dbms_output.put_line ('Error: LBN found in more than one PERIOD  !  '||
                               to_char(thisLBN));
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);
      WHEN OTHERS THEN
        dbms_output.put_line ('LT exception ------------------------------------');
        dbms_output.put_line ('ERROR '||sqlerrm);
        dbms_output.put_line ('SQL CODE : '||mySQL);

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

  end loop; 	/* end of loop over LT data */

  /*  final commit for LT */
  dbms_output.put_line ('Final LT Commit.');
  commit;

  /*  print total LT */
  dbms_output.put_line ('LT: ---------------------------------------------------');
  dbms_output.put_line ('Total LT updates = '||to_char(total_LT_count));
  dbms_output.put_line ('LT Period changes = '||to_char(count_period_changes));

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

  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;
/
