-- ================================================================================
-- E. Gallas
-- May, 2007
-- Designed to run before the columns are renamed (default and _NEW columns exist)
--
-- 1. Make sure special values are equal
--
-- 2. Make sure new luminosity is always > old luminosity
--
-- 3. See if the sum over ticks is the same as tick0 
-- 
-- ================================================================================
-- ================================================================================
spool 0705_CheckIntegrity.out;
set echo on;

desc DELIVERED_LUMS;

desc EXPOSED_LUMS;

desc LBN_TRIGGERS;

-- ================================================================================
-- ================================================================================
-- 1. Make sure special values are equal

SELECT LOCALTIMESTAMP FROM DUAL; 

select LBN,TICK_NUM,DEL_LUM_NIM,DEL_LUM_NIM_NEW,DEL_LUM_NIM_NEW-DEL_LUM_NIM
	from DELIVERED_LUMS
	where DEL_LUM_NIM_NEW!=DEL_LUM_NIM and
              (DEL_LUM_NIM < 0. or DEL_LUM_NIM is NULL or DEL_LUM_NIM = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,TICK_NUM,DEL_LUM_VME,DEL_LUM_VME_NEW,DEL_LUM_VME_NEW-DEL_LUM_VME
	from DELIVERED_LUMS
	where DEL_LUM_VME_NEW!=DEL_LUM_VME and
              (DEL_LUM_VME < 0. or DEL_LUM_VME is NULL or DEL_LUM_VME = 0.);

SELECT LOCALTIMESTAMP FROM DUAL; 

select LBN,TICK_NUM,EXP_LUM_NIM,EXP_LUM_NIM_NEW,EXP_LUM_NIM_NEW-EXP_LUM_NIM
	from EXPOSED_LUMS
	where EXP_LUM_NIM_NEW!=EXP_LUM_NIM and
              (EXP_LUM_NIM < 0. or EXP_LUM_NIM is NULL or EXP_LUM_NIM = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,TICK_NUM,EXP_LUM_VME,EXP_LUM_VME_NEW,EXP_LUM_VME_NEW-EXP_LUM_VME
	from EXPOSED_LUMS
	where EXP_LUM_VME_NEW!=EXP_LUM_VME and
              (EXP_LUM_VME < 0. or EXP_LUM_VME is NULL or EXP_LUM_VME = 0.);

SELECT LOCALTIMESTAMP FROM DUAL; 

select LBN,L1_LUM_NIM,L1_LUM_NIM_NEW
	from LBN_TRIGGERS
	where L1_LUM_NIM_NEW!=L1_LUM_NIM and
              (L1_LUM_NIM < 0. or L1_LUM_NIM is NULL or L1_LUM_NIM = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,L1_LUM_VME,L1_LUM_VME_NEW
	from LBN_TRIGGERS
	where L1_LUM_VME_NEW!=L1_LUM_VME and
              (L1_LUM_VME < 0. or L1_LUM_VME is NULL or L1_LUM_VME = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,REC_LUM_NIM,REC_LUM_NIM_NEW
	from LBN_TRIGGERS
	where REC_LUM_NIM_NEW!=REC_LUM_NIM and
              (REC_LUM_NIM < 0. or REC_LUM_NIM is NULL or REC_LUM_NIM = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,REC_LUM_VME,REC_LUM_VME_NEW
	from LBN_TRIGGERS
	where REC_LUM_VME_NEW!=REC_LUM_VME and
              (REC_LUM_VME < 0. or REC_LUM_VME is NULL or REC_LUM_VME = 0.);

-- ================================================================================
-- ================================================================================
-- 2. Make sure new luminosity is always > old luminosity

SELECT LOCALTIMESTAMP FROM DUAL; 

select count(*)
	from DELIVERED_LUMS
	where DEL_LUM_NIM_NEW-DEL_LUM_NIM<0. and
              DEL_LUM_NIM is not null and
              DEL_LUM_NIM > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from DELIVERED_LUMS
	where DEL_LUM_VME_NEW-DEL_LUM_VME<0. and
              DEL_LUM_VME is not null and
              DEL_LUM_VME > 0;

SELECT LOCALTIMESTAMP FROM DUAL; 

select count(*)
	from EXPOSED_LUMS
	where EXP_LUM_NIM_NEW-EXP_LUM_NIM<0. and
              EXP_LUM_NIM is not null and
              EXP_LUM_NIM > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from EXPOSED_LUMS
	where EXP_LUM_VME_NEW-EXP_LUM_VME<0. and
              EXP_LUM_VME is not null and
              EXP_LUM_VME > 0;

SELECT LOCALTIMESTAMP FROM DUAL; 

select count(*)
	from LBN_TRIGGERS
	where L1_LUM_NIM_NEW-L1_LUM_NIM<0. and
              L1_LUM_NIM is not null and
              L1_LUM_NIM > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from LBN_TRIGGERS
	where L1_LUM_VME_NEW-L1_LUM_VME<0. and
              L1_LUM_VME is not null and
              L1_LUM_VME > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from LBN_TRIGGERS
	where REC_LUM_NIM_NEW-REC_LUM_NIM<0. and
              REC_LUM_NIM is not null and
              REC_LUM_NIM > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from LBN_TRIGGERS
	where REC_LUM_VME_NEW-REC_LUM_VME<0. and
              REC_LUM_VME is not null and
              REC_LUM_VME > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 

-- ============
-- if there were any matches (initially I had some, 
-- I'd want to know what period they were in
-- so I wrote this query including use of the LumPeriodTable1 Table.

 desc LumPeriodTable1;

--  Name          Type
--  ------------- ----------------------------
--  PERIOD        VARCHAR2(10)
--  LBN_LO        NUMBER(38)
--  LBN_HI        NUMBER(38)

select count(*) from LumPeriodTable1;
select * from LumPeriodTable1;

SELECT LOCALTIMESTAMP FROM DUAL; 

select LPT.PERIOD,DL.LBN,DL.TICK_NUM,DL.DEL_LUM_NIM,DL.DEL_LUM_NIM_NEW,DL.DEL_LUM_NIM_NEW-DL.DEL_LUM_NIM
	from DELIVERED_LUMS DL, LumPeriodTable1 LPT
	where DL.DEL_LUM_NIM_NEW-DL.DEL_LUM_NIM<0. and
              DL.DEL_LUM_NIM is not null and
              DL.DEL_LUM_NIM > 0 and
		DL.LBN>=LPT.LBN_LO and DL.LBN<=LPT.LBN_HI
	order by LPT.PERIOD,DL.DEL_LUM_NIM_NEW-DL.DEL_LUM_NIM desc;
SELECT LOCALTIMESTAMP FROM DUAL; 
select LPT.PERIOD,count(*)
	from DELIVERED_LUMS DL, LumPeriodTable1 LPT
	where DL.DEL_LUM_NIM_NEW-DL.DEL_LUM_NIM<0. and
              DL.DEL_LUM_NIM is not null and
              DL.DEL_LUM_NIM > 0 and
		DL.LBN>=LPT.LBN_LO and DL.LBN<=LPT.LBN_HI
	group by LPT.period;

-- ================================================================================
-- ================================================================================
-- 3. See if the sum over ticks is the same as tick0
-- Thanks to Anil Kumar for initial help writing this SQL.

SELECT LOCALTIMESTAMP FROM DUAL; 

/* This script will give list of all LBNs and luminosity diff 
    between TICK_NUM = 0 and  sum of lumi for other ticks for one LBN */
------------------------
select t.LBN, (t.DEL_LUM_NIM_new-SUM_DEL_LUM_NIM) diff, t.DEL_LUM_NIM_new
	from 
	(select LBN, sum(DEL_LUM_NIM_new) SUM_DEL_LUM_NIM
	 from delivered_lums
	 where TICK_NUM <> 0
	 and LBN = 4467850
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_NIM_new
	 from delivered_lums
	 where TICK_NUM = 0
	 and LBN = 4467850 ) t
where  t.LBN = s.LBN;

-- lum/lum1       LBN              DIFF DEL_LUM_NIM_NEW
-- lum/lum1---------- ----------------- ---------------
-- lum/lum1   4467850       .0000000000  156.075232
-- lum/lumd   4467850       .0000000000  156.075232
-- gal/lumd   4467850      -.0000000004  156.075232

/* Look at the data in gallas and luminosity tables on d0oflumd
   set linesize 100;
   select g.lbn, g.tick_num, 
	g.DEL_LUM_NIM_OLD,  l.DEL_LUM_NIM_OLD,
	g.DEL_LUM_NIM_OLD - l.DEL_LUM_NIM_OLD
	from gallas.delivered_lums g, luminosity.delivered_lums l
	where g.LBN = 4467850
	and g.lbn = l.lbn and g.tick_num = l.tick_num;
*/

-- this is a test query, run only on gallas/lumd !!!
/*
    select t.LBN, (t.DEL_LUM_NIM-SUM_DEL_LUM_NIM) diff, t.DEL_LUM_NIM
	from 
	(select LBN, sum(DEL_LUM_NIM) SUM_DEL_LUM_NIM
	 from delivered_lums
	 where TICK_NUM <> 0
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_NIM
	 from delivered_lums
	 where TICK_NUM = 0
	 and DEL_LUM_NIM>0. ) t
         where  t.LBN = s.LBN;
---- highest diff in gallas/d0oflumd is 0.0000000005
----
---- results from my test database with 200 LBNs and DIFF>0.0000000001
----        LBN              DIFF
---- ---------- -----------------
----    4467843      -.0000000002
----    4467840       .0000000003
----    4467841       .0000000001
----    4467846       .0000000004
----    4467850      -.0000000003
----    4467847       .0000000001
----    4467849       .0000000004
---- 
---- 7 rows selected.

---- results from my test database with 200 LBNs and diff > 0.0000000001
----        LBN     EG_NUM              DIFF
---- ---------- ---------- -----------------
----    4467850          0      -.0000000004
----    4467845          0      -.0000000004
----    4467848          0      -.0000000004
----    4467849          0      -.0000000003
----    4467843          0       .0000000003
----    4467846          0       .0000000004
----    4467847          0      -.0000000004
----    4467844          0       .0000000002
----    4467840          0      -.0000000002
---- 
---- 9 rows selected.

*/

-- remove single LBN criteria ------------

-- NIM tick sum check:

SELECT LOCALTIMESTAMP FROM DUAL; 

column diff format 99999.9999999999;
select t.LBN, (t.DEL_LUM_NIM_new-SUM_DEL_LUM_NIM) diff, t.DEL_LUM_NIM_new
	from 
	(select LBN, sum(DEL_LUM_NIM_new) SUM_DEL_LUM_NIM
	 from delivered_lums
	 where TICK_NUM <> 0
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_NIM_new
	 from delivered_lums
	 where TICK_NUM = 0
	 and DEL_LUM_NIM_new>0. ) t
where  t.LBN = s.LBN
and abs(t.DEL_LUM_NIM_new-SUM_DEL_LUM_NIM) > 0.00000005;

SELECT LOCALTIMESTAMP FROM DUAL; 

---- for EXPOSED_LUMS, add the EG_NUM criteria
select t.LBN, t.EG_NUM, 
      (t.EXP_LUM_NIM_new-SUM_EXP_LUM_NIM) diff, t.EXP_LUM_NIM_new
	from 
	(select LBN,EG_NUM, sum(EXP_LUM_NIM_new) SUM_EXP_LUM_NIM
	 from exposed_lums
	 where TICK_NUM <> 0
	 group by LBN,EG_NUM) 		s, 
	(select LBN,EG_NUM,EXP_LUM_NIM_new
	 from exposed_lums
	 where TICK_NUM = 0 
	 and EXP_LUM_NIM>0. ) t
where  t.LBN    = s.LBN
and    t.EG_NUM = s.EG_NUM
and abs(t.EXP_LUM_NIM_new-SUM_EXP_LUM_NIM) > 0.00000005;

-- VME tick sum check:

SELECT LOCALTIMESTAMP FROM DUAL; 

select t.LBN, (t.DEL_LUM_VME_new-SUM_DEL_LUM_VME) diff, t.DEL_LUM_VME_new
	from 
	(select LBN, sum(DEL_LUM_VME_new) SUM_DEL_LUM_VME
	 from delivered_lums
	 where TICK_NUM <> 0
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_VME_new
	 from delivered_lums
	 where TICK_NUM = 0
	 and DEL_LUM_VME_new>0. ) t
where  t.LBN = s.LBN
and abs(t.DEL_LUM_VME_new-SUM_DEL_LUM_VME) > 0.00000005;

SELECT LOCALTIMESTAMP FROM DUAL; 

---- for EXPOSED_LUMS, add the EG_NUM criteria
select t.LBN, t.EG_NUM, 
      (t.EXP_LUM_VME_new-SUM_EXP_LUM_VME) diff, t.EXP_LUM_VME_new
	from 
	(select LBN,EG_NUM, sum(EXP_LUM_VME_new) SUM_EXP_LUM_VME
	 from exposed_lums
	 where TICK_NUM <> 0
	 group by LBN,EG_NUM) 		s, 
	(select LBN,EG_NUM,EXP_LUM_VME_new
	 from exposed_lums
	 where TICK_NUM = 0 
	 and EXP_LUM_VME>0. ) t
where  t.LBN    = s.LBN
and    t.EG_NUM = s.EG_NUM
and abs(t.EXP_LUM_VME_new-SUM_EXP_LUM_VME) > 0.00000005;

SELECT LOCALTIMESTAMP FROM DUAL; 


spool off;
