-- ================================================================================
-- ================================================================================
-- 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
-- ================================================================================
-- ================================================================================
-- I started thinking about individual update statements in the files here,
-- but then the update progress cannot be monitored and Greg ... might change the
-- constant values and I don't have the exact LBN ranges yet, 
-- so I am trying the plsql below instead of these files
-- so that constants and LBN period ranges go in one place only.
-- ================================================================================

/*  Define a table that stores the lbn ranges (LO and HI) for each region and their
    multiplicative constants here: 1=linear, 2=square, 3=cube.
    ALL THE VARIABLES THAT MIGHT CHANGE ARE IN THIS TABLE 
    (the LBN ranges and their constants) */

-- PROMPT Creating Table 'LumConstTable' containing the 
--        LBN boundaries (inclusive) of each data taking period and their
--        linear, square, and cubic multiplicative constants in each period.

DROP TABLE LumConstTable;
CREATE TABLE LumConstTable (PERIOD VARCHAR2(10),
                            LBN_LO INTEGER,
                            LBN_HI INTEGER,
                            CONST1 NUMBER,
                            CONST2 NUMBER,
                            CONST3 NUMBER);
DELETE FROM LumConstTable;

/* ================================================================================
*/
-- the following values were in email from Greg Snow 13-Jun-06 8:55
insert into LumConstTable values 
	('Run 2A:A', 1129853, 3736259, 1.026,     0.,          0.);
insert into LumConstTable values 
	('Run 2A:B', 3736260, 3757957, 0.975357,  0.00231534, -2.30643e-5);
insert into LumConstTable values 
	('Run 2A:C', 3757958, 3921401, 1.039764, -0.00174996,  3.12866e-5);
insert into LumConstTable values 
	('Run 2A:D', 3921405, 4317937, 1.01718,  -9.34606e-4,  7.4107e-6);
insert into LumConstTable values 
	('Run 2A:E', 4317939, 4715213, 1.125,     0.,          0.);
insert into LumConstTable values 
	('Run 2B',   4715214, 9999999, 1.000,     0.,          0.);

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

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 for the 3 constants (same names as the columns)  */
PERIOD LumConstTable.PERIOD%type;
LBN_LO LumConstTable.LBN_LO%type;
LBN_HI LumConstTable.LBN_HI%type;
CONST1 LumConstTable.CONST1%type;
CONST2 LumConstTable.CONST2%type;
CONST3 LumConstTable.CONST3%type;

/* declare variables for the new NIM and new VME luminosity values */
thisLBN LumConstTable.LBN_LO%type;
thisLum DELIVERED_LUMS.DEL_LUM_NIM%type;

/* declare a function for the new luminosity */
--NewLuminosity DELIVERED_LUMS.DEL_LUM_NIM%type := CONST1*thisLum + 
--                                                 CONST2*thisLum**2 + 
--                                                CONST3*thisLum**3;


/* I need a place to store the new luminosity columns */
newNIM DELIVERED_LUMS.DEL_LUM_NIM%type;
newVME 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) := '';

/* keep track of the previous LBN so I can reduce queries for constants */
lastLBN LumConstTable.LBN_LO%type    := 0;
lastPeriod LumConstTable.PERIOD%type := 'blah';

-- ================================================================================
/* 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);
  dbms_output.put_line ('Working on Delivered_Lums Table');

-- ================================================================================
dbms_output.put_line ('Working on LBN_TRIGGERS Table');
-- ================================================================================
/* LBN_TRIGGERS Table  ------------------------------------------------------- */
  for thisRow in LT_data loop
    begin

      thisLBN := thisRow.LBN;
      /* get constants for this LBN (print if PERIOD != last Period) */
      IF lastLBN!=thisLBN then
        BEGIN

          mySQL := 'SELECT PERIOD,LBN_LO,LBN_HI,CONST1,CONST2,CONST3 
            from LumConstTable 
            where LBN_LO<='||to_char(thisLBN)||' and LBN_HI>='||to_char(thisLBN);

          EXECUTE IMMEDIATE mySQL into PERIOD,LBN_LO,LBN_HI,CONST1,CONST2,CONST3 ;

	  EXCEPTION 
          WHEN NO_DATA_FOUND THEN 
	    dbms_output.put_line ('LT: -----------------------------------------');
  	    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);
	    dbms_output.put_line ('LT: -----------------------------------------');
	    exit;
	  WHEN TOO_MANY_ROWS THEN
	    dbms_output.put_line ('LT: -----------------------------------------');
            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);
	    dbms_output.put_line ('LT: -----------------------------------------');
	    exit;
	  WHEN OTHERS THEN
	    dbms_output.put_line ('LT: -----------------------------------------');
            dbms_output.put_line ('Other Error');
            dbms_output.put_line ('SQL CODE : '||mySQL);
            dbms_output.put_line ('ERROR '||sqlerrm);
	    dbms_output.put_line ('LT: -----------------------------------------');
	    exit;
	END;

	IF DEBUG = 'on' AND PERIOD != lastPeriod then
	  count_period_changes  := count_period_changes + 1;
	  IF count_period_changes<10 THEN
            dbms_output.put_line ('LT: -----------------------------------------');
	    dbms_output.put_line ('LT: New Period !!! '||PERIOD||
                       '  ('||LBN_LO||'-'||LBN_HI||') for LBN '||to_char(thisLBN));
          END IF;
        END IF;

      END IF;
      lastPeriod := PERIOD;
      lastLBN := thisLBN;

-- ================================================================================
-- Simply multiply all Run 2A luminosities by 1.155
      IF PERIOD!='Run 2B' THEN
        CONST1 := 1.155;
        CONST2 := 0.;
        CONST3 := 0.;
      ELSE
	CONST1 := 1.000;
      END IF;
-- ================================================================================

      /* Calculate new L1_LUM_NIM luminosity ----------------------------------- */
      thisLUM   := thisRow.L1_LUM_NIM;
      if thisLUM > 0. then
--old	new_L1NIM := CONST1*thisLum + CONST2*thisLum**2 + CONST3*thisLum**3;
        new_L1NIM := CONST1 * thisLum;
      ELSE
        new_L1NIM := thisLum;
      END IF;

      /* Calculate new L1_LUM_VME luminosity ----------------------------------- */
      thisLUM := thisRow.L1_LUM_VME;
      if thisLUM > 0. then
--old	new_L1VME := CONST1*thisLum + CONST2*thisLum**2 + CONST3*thisLum**3;
        new_L1VME := CONST1 * thisLum;
      ELSE
        new_L1VME := thisLum;
      END IF;

      /* Calculate new REC_LUM_NIM luminosity ----------------------------------- */
      thisLUM := thisRow.REC_LUM_NIM;
      if thisLUM > 0. then
--old	new_RECNIM := CONST1*thisLum + CONST2*thisLum**2 + CONST3*thisLum**3;
        new_RECNIM := CONST1 * thisLum;
      ELSE
        new_RECNIM := thisLum;
      END IF;

      /* Calculate new REC_LUM_VME luminosity ----------------------------------- */
      thisLUM := thisRow.REC_LUM_VME;
      if thisLUM > 0. then
--old	new_RECVME := CONST1*thisLum + CONST2*thisLum**2 + CONST3*thisLum**3;
        new_RECVME := CONST1 * thisLum;
      ELSE
        new_RECVME := thisLum;
      END IF;

      /* 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 OTHERS THEN
	  dbms_output.put_line ('Update LT: ------------------------------------');
          dbms_output.put_line ('ERROR '||sqlerrm);
  	  RAISE_APPLICATION_ERROR(-20000,'LT: RAISE_APPLICATION_ERROR');

    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));
-- ================================================================================

EXCEPTION 
  WHEN OTHERS THEN
    dbms_output.put_line ('End of program: -------------------------------------');


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


















