#Script to assess completeness based on a single calibration field
#Usage:   docal 90004 s {0/1)  -- does the analysis for 
#field 90004 in the South for either north or south-going scans
#
psql -e -c "drop table temp" wsdb
#
psql -e -c "create table temp as select min(jpsp) as minjpsp from qacit where tile=$1 and hemis='$2' and s=$3" wsdb
#
psql -e -c "drop table s$1$2$3" wsdb
#
psql -e -c "create table s$1$2$3 as select a.cntr,a.ra,a.decl,j_m,h_m,k_m,ph_qual,scan_key from pub_v3_cwsdb as a inner join qacit as b using (scan_key) where b.scan_key = (select min(scan_key) from qacit where tile=$1 and hemis='$2' and s=$3 and jpsp=(select minjpsp from temp)) and ph_qual like 'A__' and cc_flg!~'[A-Z]' and s=$3 " wsdb
#
psql -e -c "drop table r$1$2$3" wsdb
#
psql -e -c "create table r$1$2$3 as select a.scan_key,a.ra,a.decl,j_m,h_m,k_m,ph_qual,ndet from pub_v3_cwsdb as a inner join qacit as b using (scan_key) where b.tile=$1 and a.hemis='$2' and ph_qual like 'A__'  and cc_flg!~'[A-Z]' and s=$3 " wsdb
#
psql -e -c "create index r$1$2$3_indx_radec on r$1$2$3 (ra,decl)" wsdb
#
psql -e -c "analyze r$1$2$3" wsdb
#
psql -e -c "drop table temp" wsdb
#
psql -e -c "create table temp as select min(ra) as minra ,min(decl) as mindecl,max(ra) as maxra,max(decl) as maxdecl,scan_key from r$1$2$3 group by scan_key" wsdb
#
psql -e -c "drop table a$1$2$3" wsdb
#
psql -e -c "create table a$1$2$3 as select count(*),max(a.ph_qual) as ph_qual,a.cntr,round(avg(b.ra),5)::float as ra,round(avg(b.decl),5)::float as decl,round(avg(b.j_m),3) as j_m,round(avg(b.h_m),3) as h_m,round(avg(b.k_m),3) as k_m from s$1$2$3 as a, r$1$2$3 as b where b.ra between a.ra-0.00056 and a.ra+0.00056 and b.decl between a.decl-0.00056 and a.decl+0.00056 group by a.cntr" wsdb
#
psql -e -c "drop table jA$1$2$3" wsdb
#
psql -e -c "create table jA$1$2$3 as select round(100.*count::float/(select count::float from cals_$2$3 where tile=$1),2)::float,* from a$1$2$3  where ra>(select max(minra) from temp)+0.005 and ra<(select min(maxra) from temp)-0.005 and decl>(select max(mindecl) from temp)+0.005 and decl<(select min(maxdecl) from temp)-0.005 ORDER BY count DESC" wsdb
#
psql -e -c "drop table a$1$2$3" wsdb
psql -e -c "drop table r$1$2$3" wsdb
psql -e -c "drop table s$1$2$3" wsdb
#