#!/usr/bin/env perl
########!/usr/local/bin/perl -w
#   Prepare the list of Catalogue with Suppl. Material (\vizContent)
#================================================

use Sybase::DBlib;
#### To get the correct order in stderr/stdout,
#### this $!*? perl must unflush and redirect
select(STDERR); $| = 1 ;
select(STDOUT); $| = 1 ;
open(STDERR, ">&STDOUT") || die "***Can't redirect stderr" ;

##########################################################
# Execute a single request -- $_[0] = dbh $_[1] = SQL $_2 = Print
##########################################################
sub dbexec1 {
    local($dba, $nrec)  ;
    $dba = $_[0] ;
    #$dba->dbcmd($_[1]) ; $dba->dbsqlexec ; $dba->dbresults;
    $dba->sql($_[1]) ;
    $nrec = $dba->DBCOUNT ;
    #$dba->dbcancel ;
    if ($_[2]>0) { print "%---$_[1]\n% =>$nrec\n" }
    return($nrec) ;
}

sub msg0 {	# Ignore the error
    return(1) ;
}
sub err0 {	# Ignore the error
    return(INT_CANCEL) ;
}

##########################################################
#  Connect to Sybase
##########################################################

BCP_SETL(TRUE) ;
$dbh = new Sybase::DBlib('asu', 'asu4VizieR', 'SYBASE')  
     || die "****Can't connect to database???" ;
$dbt = new Sybase::DBlib('asu', 'asu4VizieR', 'SYBASE')  
     || die "****Can't connect for input???"   ;

$old_msg = &dbmsghandle (\&msg0) ;
$old_err = &dberrhandle (\&err0) ;
#$dbh->dbuse("tempdb") ;
if (defined($old_msg)) { &dbmsghandle ($old_msg) }
else { &dbmsghandle (undef) }
if (defined($old_err)) { &dberrhandle ($old_err) }
else { &dberrhandle (undef) }

##########################################################
# Write Header (macro definition)
##########################################################
print "\\cgidef{-tex -oglu --ic 
\\vCat #1 {{\\bf(\\A{/viz-bin/VizieR?-meta.foot&-source=#1}{#1})}}
}
" ;

##########################################################
# Select various Contents
# %Contents keeps the list of Contents.
##########################################################

&dbexec1($dbt, "Create table #cats(type varchar(50), catid integer)", 1);

$dbh->dbcmd("Select text,catid From METAmor where text like '%\vizContent%'") ;
$dbh->dbsqlexec ; $dbh->dbresults;
while(@data = $dbh->dbnextrow) {
    # Keep only the contents of \vizContent{...}
    # ... and count only once per catalog !
    $_ = $data[0]; s/^.*\\vizContent[{]//; s/[}].*$//; s/\/.*$//; 
    $catid = $data[1];
    if ($theCat{$_} != $catid) { 
	$Contents{$_} += 1 ; 
	if ($Contents{$_} == 1) { 
	    print "%...New Content '$_' (METAmor, catid=$catid)\n";
	}
	$theCat{$_} = $catid ;
        &dbexec1($dbt, "Insert into #cats values ('$_', $catid)", 0);
    }
}
print "%====List of various Contents from METAmor:\n";
foreach $k (sort(keys(%Contents))) { 
    printf "%% %4d with '%s'\n", $Contents{$k}, $k 
}

### There are also links for Tables and Catalogues
$dbh->dbcmd("Select explain,catid From METAtab where explain like '%\vizContent%'");
$dbh->dbsqlexec ; $dbh->dbresults;
while(@data = $dbh->dbnextrow) {
    # Keep only the contents of \vizContent{...}
    # ... and count only once per catalog !
    $_ = $data[0]; s/^.*\\vizContent[{]//; s/[}].*$//; s/\/.*$//; 
    $catid = $data[1];
    if ($theCat{$_} != $catid) { 
	$Contents{$_} += 1 ; 
	if ($Contents{$_} == 1) { 
	    print "%...New Content '$_' (METAtab, catid=$catid)\n";
	}
	$theCat{$_} = $catid 
        &dbexec1($dbt, "Insert into #cats values ('$_', $catid)", 0);
    }
}
$dbh->dbcmd("Select explain,catid From METAcat where explain like '%\vizContent%'");
$dbh->dbsqlexec ; $dbh->dbresults;
while(@data = $dbh->dbnextrow) {
    # Keep only the contents of \vizContent{...}
    # ... and count only once per catalog !
    $_ = $data[0]; s/^.*\\vizContent[{]//; s/[}].*$//; s/\/.*$//; 
    $catid = $data[1];
    if ($theCat{$_} != $catid) { 
	$Contents{$_} += 1 ; 
	if ($Contents{$_} == 1) { 
	    print "%...New Content '$_' (METAcat, catid=$catid)\n";
	}
	$theCat{$_} = $catid 
        &dbexec1($dbt, "Insert into #cats values ('$_', $catid)", 0);
    }
}

print "%====List of various Contents with METAtab:\n";
foreach $k (sort(keys(%Contents))) { 
    printf "%% %4d with '%s'\n", $Contents{$k}, $k 
}

#print "\\title{CDS catalogues M}";
print "\\CDShead{CDS Catalogues with additional Material}{VizieR.logo}";
#print "\\begin{html}<head><&CDS.headStuff2></head><body>"; \
#print "<&CDS.headArea2 \"CDS Catalogues with additional Material\" \"vizier\">\\end{html}";

# Write the TOC
print "%\\par\\tableofcontents\\par\n";
print "\\begin{enumerate}\n"; 
#print "\\Beg{TABULAR}{\\bg{honeydew} CELLPADDING=5 CELLSPACING=0}{||pp}
$n=1;
foreach $k (sort(keys(%Contents))) {
    print "\\item\\Aref{ToC$n}{Catalogues with {\\fg{red3}$k}}\n"; $n += 1;
}
print "\\end{enumerate}\n"; 
print "{\\small\\em(Click on a catalogue name or number to move to the 
 corresponding VizieR page)}\n";
print "\\hr\n";
foreach $k (sort(keys(%Contents))) {
    $aw = "";
    if ($k eq "spectrum") { $aw .= " (or SED, line profile)" }
    if ($k eq "filter")   { $aw .= " (response curves)" }
    print "\n\\section{Catalogues with {\\fg{red3}$k}$aw}\n";
    $cmd = "Select catid, name, title from METAcat"
       . " where catid in (Select catid from #cats where type='$k')"
       . " order by catid" ;
    $dbt->dbcmd($cmd) ;
    $dbt->dbsqlexec ; $dbt->dbresults;
    while(@data = $dbt->dbnextrow) {
	print "\\vCat{$data[1]}\\quad $data[2]\\\\\n" ;
    }
    #$cmd = "Select distinct METAcat.catid, name, title from METAcat, METAmor"
    #   . " where text like '%izContent{$k%}%'"
    #   . "   and METAcat.catid = METAmor.catid"
    #   . " order by METAcat.catid" ;
    #$dbh->dbcmd($cmd) ;
    #$dbh->dbsqlexec ; $dbh->dbresults;
    #while(@data = $dbh->dbnextrow) {
    #	print "\\vCat{$data[1]}\\quad $data[2]\\\\\n" ;
    #}
}
print "\\CDStail{The Catalogue of Catalogues (\\basename{\\thefile})}\n" ;

#print "\\begin{html}<&CDS.tailArea2 \"CDS Catalogues with additional Material\" \" \" \"vizier\"></body>\\end{html}";
exit 0;