#!/usr/bin/perl
eval "exec /usr/local/bin/perl -S $0 $*"
    if $running_under_some_shell;
               # this emulates #! processing on NIH machines.
               # (remove #! line above if indigestible)

eval '$'.$1.'$2;' while $ARGV[0] =~ /^([A-Za-z_0-9]+=)(.*)/ && shift;
               # process any FOO=bar switches

$USERNAME="***";
$PASSWORD="***";
$SERVER  ="***";

BEGIN { unshift @INC,'/home/spatters/tcad/dc/cdf30-dist/PerlCDF30/blib/arch','/home/spatters/tcad/dc/cdf30-dist/PerlCDF30/blib/lib' }
use CDF;				#Load the CDF interface module.
use DBI;				#Load the Database interface module.

#Parse up the commandline!  The first 5 params are hard and fast.  The rest are the 
#fields to extract.
if ($#ARGV < 3) {
   printf ("Invalid syntax.  Use: CDFarc.pl <DB> <TABLE> <CONDITIONS> <COL1> [COL2 .. COLN]");
   printf("  DB		Name of sybase DB (EX: GPB_L1)\n");
   printf("  TABLE	Name of sybase Table (EX: TMnames)\n");
   printf("  CONDITIONS	Conditions for select use '' for none.  (EX: Cycle=12)\n");
   printf("  COL#	Columns to be extracted.  (EX:  TMID, Value)\n");
   exit
}

#Get the static params from the command line and shift them off the list.
$DB         = @ARGV[0]; shift;	
$TABLE      = @ARGV[0]; shift;
$CONDITIONS = @ARGV[0]; shift;
@COLS = @ARGV;			#Copy columns to list for clarity.
@COLTYPE=('');			#Create stubs for later population.
@COLLEN =(0);			#Ditto.

#Construct and execute ISQL command to fetch table info.
$CMD="\"use $DB\\ngo\\nsp_help $TABLE\\ngo\\n\"";
$result=`/usr/bin/echo $CMD|isql -U$USERNAME -P$PASSWORD -w3000`;

#Verify that we got the DB and Table we thought we did.
if ($result =~ "Attempt to locate entry in sysdatabases for database") { 
   die "Database $DB not found in sybase.\n";
}

if ($result =~ "Object does not exist in this database.") {
   die "Table $TABLE not found in $DB.\n";
}


#Now we go through and look for fields that match our inputs.
my $infields=0;			#Set to 1 when in field section.
@lines = split('\n',$result);
foreach $line (@lines) {
   @words=split(" ",$line);

   if (@words[0] eq "Column_name"     && $infields==0) {  #Start comparing fields to params.
      $infields=1;
   }
   if (@words[0] eq "attribute_class" && $infields==1) {  #End of field defs.
      $infields=0;
   }

   if ($infields) {	#Compare params to 0th field.
      for ($i=0;$i<=$#COLS;$i++) {
        if ( @COLS[$i] eq @words[0] ) {
           @COLTYPE[$i]=@words[1];
           @COLLEN[$i] =@words[2];
        }
      }
   }
}

#Make sure we found all the fields the user wanted to find.
for ($i=0;$i<=$#COLS;$i++) {
   if ($#COLLEN < $i || !@COLLEN[$i]) {
      die "@COLS[$i] not found in $TABLE\n"
   }
}

######################OPEN CDF FILE FOR OUTPUT##################
#Do this here so we don't waste time if it's going to bomb.
my $CDFid;
my $numDims=0;
my @dimSizes=(1);

$filename = $TABLE;		#Generate the output table name.

$result=`/bin/rm -f $filename.cdf`;	#Clear old copy if exists.

#Open file.  If already exists, delete and try again.
$status=CDF::CDFcreate($filename,$numDims,\@dimSizes,NETWORK_ENCODING,ROW_MAJOR,\$CDFid);
if ($status < CDF_OK) {
  die "Failed to open CDF file for output:  " . $status
}

#######################SELECT THE DATA FROM SQL######################

#Connect to the server and create a handle to the pipe
$hDB = DBI->connect("dbi:Sybase:server=GPB_DATA",$USERNAME,$PASSWORD) || die $DBI::errstr . "\n";

$sqlcmd="select";		#Build command string for SQL prepare statment.
for ($i=0;$i<=$#COLS;$i++) {			#Append fields to get to command.
  if ($i > 0) { $sqlcmd = $sqlcmd . ","; }	#Append comma after 1st statement.
  $sqlcmd = $sqlcmd . " " . @COLS[$i];
}
$sqlcmd = $sqlcmd . " from " . $DB . ".." . $TABLE;	#Append DB and table.

if ($CONDITIONS NE '') {			#Append WHERE clause.
   $sqlcmd = $sqlcmd . " WHERE ";
}

if ($CONDITIONS NE '') {			#Append conditions (if any)
   $sqlcmd = $sqlcmd . $CONDITIONS
}

print "SQLCMD is $sqlcmd\n";
$hCursor=$hDB->prepare($sqlcmd);		#Get this sucker ready for SQL.
$hCursor->execute() || die $DBI::errstr . "\n";	#Do or die time!

local ($liCount)=0;

while (my @Fields = $hCursor->fetchrow_array) {	#Get rows from DB.
   for ($i=0;$i<=$#COLS;$i++) {
      $Data{@COLS[$i]}[$liCount]=@Fields[$i];
   }
   $liCount++;
}

#######################OUTPUT THE DATA TO CDF########################
my $varVary=VARY;
my @dimVary=(VARY);

my $varNum_out;
my $numElements;

for ($i=0;$i<=$#COLS;$i++) {	#This is it!  Create these variables and we're done!
   $numElements=1;		#Only used for strings.
   if (     @COLTYPE[$i] eq "smallint") {	#2
      $type=CDF_INT2;
   } elsif (@COLTYPE[$i] eq "int") {		#4
      $type=CDF_INT4;
   } elsif (@COLTYPE[$i] eq "tinyint") {	#1
      $type=CDF_UINT1;
   } elsif (@COLTYPE[$i] eq "float") {		#8
      $type=CDF_DOUBLE;
   } elsif (@COLTYPE[$i] eq "datetime") {	#8 Date.
      $type=CDF_EPOCH;
   } elsif (@COLTYPE[$i] =~ "char") {		#Char.
      $numElements=@COLLEN[$i];
      $type=CDF_CHAR;
   } elsif (@COLTYPE[$i] =~ "binary") {		#Char.
      $numElements=@COLLEN[$i];
      $type=CDF_CHAR;
   } else {
      $type=CDF_INT2;		#Default to short integer for output type.
print "No match, set to smallint\n";
   }

   $status=CDF::CDFvarCreate($CDFid,@COLS[$i], $type,$numElements,$varVary,\@dimVary,$varNum_out); 
   if ($status < CDF_OK) { die "Failed VarCreate with " . $status; }

   my $var_num=CDF::CDFvarNum($CDFid,@COLS[$i]);
   if ($status < CDF_OK) { die "Failed VarNum with " . $status; }

   @cdf_data=@{$Data{@COLS[$i]}};
   @indices=0;
   @counts=1;
   @intervals=1;
   $status=CDF::CDFvHpPut($CDFid,$var_num,0,$#cdf_data,1,\@indices,\@counts,\@intervals,\@cdf_data);
   if ($status < CDF_OK) { die "Failed VarPut with " . $status; }
}

$status=CDF::CDFclose($CDFid);