use File::Basename;
$|=1;
$sqlJava="/usr/java/jdk1.6.0_26/bin/java -Djava.net.preferIPv4Stack=true -classpath .:/home/mar/jtds-1.1.jar SimpleSQL";
$sqlJava="/usr/java/jdk1.7.0_05/bin/java -Djava.net.preferIPv4Stack=true -classpath .:/home/mar/jtds-1.1.jar:/disk51/mar/ SimpleSQL";
$initialSqlQuery = <<'END_ISQL';
select t.multiframeid,year(utdate) as yy ,month(utdate) as mm ,min(obsname) as obsname,minra,maxra,mindec,maxdec,
min(medianzp) as minzp,max(medianzp) as maxzp,
min(medianseeing) as minseeing,max(medianseeing) as maxseeing,
count(*) as num, max(t.filename) as fname,max(t.deprecated) as dep from multiframe as t, provenance as p, multiframeesokeys as e,
currentastrometry as c,vmcmedianzp as z,vmcmedianseeing as s
where t.frametype like 'tilestack'
and t.utdate between '2011-04-01' and '2012-03-31' and project like '%vmc%' 
and p.combiframeid=t.multiframeid and t.filterid=5
and t.multiframeid=e.multiframeid 
and t.multiframeid=c.multiframeid and t.deprecated < 128 and
z.multiframeid=p.multiframeid and s.multiframeid=p.multiframeid 
group by t.multiframeid,year(utdate),month(utdate),obsname,minra,maxra,mindec,maxdec
order by max(medianzp)-min(medianzp) desc
END_ISQL

open (HTML,'>summaryK.html');
print HTML "<table border=\"1\" ><tr>\n";
print HTML "<th>MFID</th>";
print HTML "<th>Dep</th>";
print HTML "<th>name</th>";
print HTML "<th>tile</th>";
print HTML "<th>ZP<br>range</th>";
print HTML "<th>seeing<br>range</th>";
print HTML "<th>median<br>mag<br>diff<br>off1</th>";
print HTML "<th>median<br>mag<br>diff<br>off2</th>";
print HTML "<th>median<br>mag<br>diff<br>off3</th>";
print HTML "<th>median<br>mag<br>diff<br>off4</th>";
print HTML "<th>median<br>mag<br>diff<br>off5</th>";
print HTML "<th>median<br>mag<br>diff<br>off6</th>\n";
print HTML "<th>2MASS</th>";
print HTML "<th>off1</th>";
print HTML "<th>off2</th>";
print HTML "<th>off3</th>";
print HTML "<th>off4</th>";
print HTML "<th>off5</th>";
print HTML "<th>off6</th>\n";
print HTML "<tr>";
$initialSqlQuery=~ s/\n/ /g;

$initialQueryOut="vmcmfK.csv";

print "$initialSqlQuery \n";

unless (-e $initialQueryOut){
$out=`$sqlJava sa ibis1243\! vsa ramses16 "$initialSqlQuery" > $initialQueryOut`;
print "$out \n";
}


$stiltsCMD="stilts tmatch2 matcher=sky ifmt1=CSV ifmt2=CSV  ocmd='addcol magdiff k_m-apermag3; select \"k_m > 10 && k_m < 14.5 && averageconf > 80 && apermag3err < 0.1\"' ofmt=ASCII find=best join=1and2 params=1 values1='radeg decdeg' values2='radeg decdeg' ";
$plotCMD="python plot.py ";
open(CSV,'<vmcmfK.csv');

$lineNo=0;

while (<CSV>) {
    if ($lineNo > 0) {
	chomp;
        $line=$_;
	@csvBits = split(/,/,$line);
        $mfid=$csvBits[0];
        $year=$csvBits[1];
        $month=$csvBits[2];
	$obsName=$csvBits[3];
	$onStrt=0;
        @onBits=split(/\_/,$obsName);
	$nBits=@onBits;
	print "$nBits , $obsName $onStrt\n";
	for ($i=$nBits; $i>0; $i--){
	    print "$i $onBits[$i] \n";
	    if ($onBits[$i] =~ /[a-zA-Z]/) {
		$onStrt=$i;
		last
	    }
	}
	
        print "$onStrt\n";
	$newName="";
	for ($i=$onStrt; $i<=$nBits; $i++){
	    $newName=$newName."_".$onBits[$i];
	}
	$newName=~ s/.xml//g;
	print "$newName\n";
	 
	
        $minRA=$csvBits[4];
        $maxRA=$csvBits[5];
	$minDec=$csvBits[6];
        $maxDec=$csvBits[7];
        $zpDiff=$csvBits[9]-$csvBits[8];
	$seeingDiff=$csvBits[11]-$csvBits[10];
        
	my($fName, $fdirs, $fsuffix) = fileparse($csvBits[13]);
	print "$fName\n";
	$deprecated=$csvBits[14];

	
        $rTable='vmcDetectionRaw'; # .$year.sprintf("%02d",$month);
        $pTable='vmcDetectionPhotometry'; #.$year.sprintf("%02d",$month);
        $aTable='vmcDetectionAstrometry'; #.$year.sprintf("%02d",$month);
	print "$mfid $pTable\n";
        $sql="select ra as radeg,dec as decdeg, apermag3,x,y,averageconf,apermag3err from $rTable as r, $pTable as p, $aTable as a where r.multiframeid=$mfid and r.multiframeid=p.multiframeid and r.extnum=p.extnum and r.seqnum=p.seqnum  and a.multiframeid=p.multiframeid and a.extnum=p.extnum and a.seqnum=p.seqnum and ra >0";
        print "$sql \n";
        $csvFile=$mfid.".csv";
        unless (-e $csvFile){
        $out=`$sqlJava sa ibis1243\! vsa ramses9 "$sql" > $csvFile`;
	}
        print "$minRA $maxRA $minDec $maxDec\n";
	    $sql2="select ra as radeg,dec as decdeg, k_m from twomass_psc where ra between $minRA and $maxRA and dec between $minDec and $maxDec ";
	print "$sql2 \n";        
        $csvFile2=$mfid."_2mass.csv";

	
        unless (-e $csvFile2){
	    $out=`$sqlJava sa ibis1243\! twomass ramses10 "$sql2" > $csvFile2`;
        }



    	$csvFileO=$mfid."_off.csv";
        $osql="select ra as radeg,dec as decdeg, apermag3 as pawMag,averageconf as pawAverageConf,apermag3err as pawMagErr,t.* from $rTable as r, $pTable as p, $aTable as a, (select m.multiframeid, offsetid from Provenance as v, multiframe as m where v.combiframeID=$mfid and v.multiframeid=m.multiframeID and m.frametype like 'stack') as t where r.multiframeid=t.multiframeid  and r.multiframeid=p.multiframeid and r.extnum=p.extnum and r.seqnum=p.seqnum  and a.multiframeid=p.multiframeid and a.extnum=p.extnum and a.seqnum=p.seqnum and ra >0";
	print "$osql \n";
	unless (-e $csvFileO){
	    $out=`$sqlJava sa ibis1243\! vsa ramses9 "$osql" > $csvFileO`;
	}
	
	
	print "$stiltsCMD \n";
        $outFile=$mfid."_out.asc";
        $stilts=$stiltsCMD. " in1=$csvFile2 in2=$csvFile out=$outFile";
	print "$stilts \n";
	$countL=0;
	$countL = `wc -l < $csvFile`;
	print "$csvFile LINES $countL\n";
	unless (-e $outFile){

	    if ($countL > 1){
		print "$countL STILTS \n";
		$out=`$stilts`;
	    }
        } 
        $medV="";
	$jpegList="";
	for ($o=1; $o<=6; $o++){
        $outFileO=$mfid."_off".$o."_out.asc";
	
        $oStiltsCMD="stilts tmatch2 matcher=sky ifmt1=CSV ifmt2=CSV  ocmd='addcol magdiff pawMAG-apermag3; select \"offsetid==$o && apermag3 > 12 && apermag3 < 17.5 && averageconf > 80 && apermag3err < 0.1\"' ofmt=ASCII find=best join=1and2 params=1 values1='radeg decdeg' values2='radeg decdeg' ";
	$stilts=$oStiltsCMD. " in1=$csvFileO in2=$csvFile out=$outFileO";
	print "$stilts \n";
	print "$outFileO\n";
	unless (-e $outFileO){
	    if ($countL > 1){
				print "$countL STILTS 2 \n";
		$out=`$stilts`;
		}
	}
	
	$jpegFile="VMC_K".$mfid."_off".$o.".jpg";
	$title="\"".$newName." $fName ".$mfid." offset ".$o." tile-paw apermag3Diff \n zpDiff= ".sprintf("%.2f",$zpDiff)." seeingDiff=".sprintf("%.2f",$seeingDiff*0.34)."\"";
	$plot=$plotCMD.$outFileO." ".$jpegFile." ".$title." 10 11 15";
	unless (-e $jpegFile){
	    print "$plot\n";
	    if (-e $outFileO){
		$out=`$plot`;
	    } else {
		print "$outFileO DOES NOT EXIST \n";
	    }
#	    print "$out \n";;
	}
	$outFileOmed=$mfid."_off_median".$o."_out.txt";
	unless (-e $outFileOmed) {
	    print "DOES NOT EXIST $outFileOmed\n";
	    if (-e $outFileO){
		$out=`python median.py $outFileO 15 6 > $outFileOmed`
	    }
	}
	if (-e $jpegFile) {
	    $jpegList=$jpegList."<td><a href=\"".$jpegFile."\"><img width=\"100\" src=\"".$jpegFile."\"></a></td>";
	} else {
	    $jpegList=$jpegList."<td>NONE</td>";
	}
	if (-e $outFileOmed){
	    
	    open( MFILE,$outFileOmed);
	    chomp($firstLine = <MFILE>);
	    @lBits = split(/=/,$firstLine);
	    $lBits[1] =~ s/^\s+|\s+$//g;
            print  "XXX $lBits[1] XXX $outFileOmed\n";
	    $medV=$medV."<td>".sprintf("%.3f",$lBits[1])."</td>";
            close(MFILE);

	} else {
	    $medV=$medV."<td>NULL</td>";
	}
	}




	
        $jpegFile="VMC_K".$mfid.".jpg";
        $title="\"".$newName." $fName ".$mfid." 2MASS k_m - apermag3 \n zpDiff= ".sprintf("%.2f",$zpDiff)." seeingDiff=".sprintf("%.2f",$seeingDiff*0.34)."\"";
        $plot=$plotCMD.$outFile." ".$jpegFile." ".$title." 6 7 11";
        print "$plot\n";

        unless (-e $jpegFile){
	    $out=`$plot`;
            print "$out \n";;
	}
	if (-e  $jpegFile){
	    $jpegList="<td><a href=\"".$jpegFile."\"><img width=\"100\" src=\"$jpegFile\"></a></td>".$jpegList;
	} else {
	    $jpegList="<td>NONE</td>".$jpegList;
	}
	print HTML "<td>$mfid</td>\n";
	    print HTML "<td>$deprecated</td>\n";
    print HTML "<td>$newName</td>\n";
    print HTML "<td>$fName</td>\n";
    print HTML "<td>".sprintf("%.2f",$zpDiff)."</td>\n";
    print HTML "<td>".sprintf("%.2f",$seeingDiff*0.34)."</td>\n";
    print HTML "$medV\n";
	print HTML "$jpegList\n";
	print HTML "<tr>\n";
#    close(HTML);
#    exit;
    }

    $lineNo++;
}
close (CSV);
print HTML "</table>";
close(HTML);
#/usr/java/jdk1.6.0_26/bin/java -Djava.net.preferIPv4Stack=true -classpath .:/home/mar/jtds-1.1.jar SimpleSQL sa ibis1243! vsa ramses9 'select top 10 * from filter'