-- ================================================================================
--
-- note: This function worked but was replaced with
-- the functions defined in 
@060712_f_GetLumPeriod.sql  - GetLumPeriod gets Period based on input LBN
@060712_f_GetCorrectedLum.sql - GetCorrectedLum based on input lum,lumtick0,Period
--
-- ================================================================================
-- EG inserted this line so that this function will not execute

do not execute this SQL, it is out of date

-- ================================================================================
-- E.Gallas - July 2006 
-- Function getCorrectedLum calculates the corrected luminosity 
-- in all Run 2 data taking periods.
-- INPUT:  inLum  - the input Luminosity (based on previous lum constant)
--         inLum0 - a tick0 luminosity if that should be used to calculate ffactor
--         inLBN - the input LBN
-- OUTPUT: outLum - the corrected Luminosity
-- 
-- The final correction uses a 'back propigation' method:
-- Each of the 6 periods has its own 3 multiplicative constants
--      1=linear, 2=square, 3=cube.
-- Note: earlier periods adopt additional factors for each of the later periods.
-- 
-- ================================================================================

CREATE OR REPLACE FUNCTION getCorrectedLum 
	(inLum IN NUMBER,
	inLum0 IN NUMBER,
	inLBN  IN INTEGER)
	RETURN NUMBER IS outLum NUMBER;
--test	RETURN VARCHAR2 IS V_PERIOD VARCHAR2(10);

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

  ffactor NUMBER;

--test  outLum NUMBER;
  V_PERIOD VARCHAR2(10) := 'blah';
  LBN_LO INTEGER;

  C2AA1  NUMBER :=  1.026;
  C2AA2  NUMBER :=  0.00 ;
  C2AA3  NUMBER :=  0.00 ;

  C2AB1  NUMBER :=  0.975357;
  C2AB2  NUMBER :=  0.00231534;
  C2AB3  NUMBER := -2.30643e-5;

  C2AC1  NUMBER :=  1.039764   ;
  C2AC2  NUMBER := -0.00174996 ;
  C2AC3  NUMBER :=  3.12866e-5 ;

  C2AD1  NUMBER :=  1.01718 ;
  C2AD2  NUMBER := -9.34606e-4 ;
  C2AD3  NUMBER :=  7.4107e-6 ;

  C2AE1  NUMBER :=  1.125 ;
  C2AE2  NUMBER :=  0.00 ;
  C2AE3  NUMBER :=  0.00 ;

  C2B1   NUMBER :=  1.00 ;
  C2B2   NUMBER :=  0.00 ;
  C2B3   NUMBER :=  0.00 ;

  BEGIN
    mySQL := 'SELECT PERIOD,LBN_LO from LumPeriodTable1 where LBN_LO<='||to_char(inLBN)||' and LBN_HI>='||to_char(inLBN);
    EXECUTE IMMEDIATE mySQL into V_PERIOD,LBN_LO;

--    IF INLBN=LBN_LO THEN
--      dbms_output.put_line ('SQL CODE : '||mySQL);
--      dbms_output.put_line ('INPUT: '||inLum||inLum0||inLBN);
--    END IF;

    IF inLum > 0. then

      ffactor := 1.00;

      IF V_PERIOD <= 'Run 2A:E' THEN
        ffactor := ffactor * (C2AE1 + C2AE2*inLum0 + C2AE3*inLum0**2);
      END IF;    /* end if on AE */

      IF V_PERIOD <= 'Run 2A:D' THEN
        ffactor := ffactor * (C2AD1 + C2AD2*inLum0 + C2AD3*inLum0**2);
      END IF;    /* end if on AD */

      IF V_PERIOD <= 'Run 2A:C' THEN
        ffactor := ffactor * (C2AC1 + C2AC2*inLum0 + C2AC3*inLum0**2);
      END IF;    /* end if on AC */

      IF V_PERIOD <= 'Run 2A:B' THEN
        ffactor := ffactor * (C2AB1 + C2AB2*inLum0 + C2AB3*inLum0**2);
      END IF;    /* end if on AB */

      IF V_PERIOD <= 'Run 2A:A' THEN
        ffactor := ffactor * (C2AA1 + C2AA2*inLum0 + C2AA3*inLum0**2);
      END IF;    /* end if on AA */

-- IF V_PERIOD in ('Run 2A:E','Run 2A:D','Run 2A:C','Run 2A:B','Run 2A:A') THEN
-- IF V_PERIOD in ('Run 2A:D','Run 2A:C','Run 2A:B','Run 2A:A') THEN
-- IF V_PERIOD in ('Run 2A:C','Run 2A:B','Run 2A:A') THEN
-- IF V_PERIOD in ('Run 2A:B','Run 2A:A') THEN
-- IF V_PERIOD in ('Run 2A:A') THEN

      IF V_PERIOD = 'blah' THEN
        ffactor := -666.;
      END IF;

      outLum := ffactor * inLum;

    ELSE
      outLum := inLum;

    END IF;

   RETURN outLum; 
--test   RETURN V_PERIOD; 

  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line ('Function getCorrectedLum exception ----------');
  	dbms_output.put_line ('Error: LBN not found in known LBN periods !  '||
                               to_char(inLBN));
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);
    WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line ('Function getCorrectedLum exception ----------');
        dbms_output.put_line ('Error: LBN found in more than one PERIOD  !  '||
                               to_char(inLBN));
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);
    WHEN OTHERS THEN
        dbms_output.put_line ('Function getCorrectedLum exception ----------');
        dbms_output.put_line ('Other Error');
        dbms_output.put_line ('SQL CODE : '||mySQL);
        dbms_output.put_line ('ERROR '||sqlerrm);

 END;
/
