#!/usr/bin/perl
#
# sy_report - inspired by Informix' Ace Report Writer
#
# sy_report [options] report_definition_file [parameters] [parameter...]
#
# options:
#          -D: optional database name
#          -U: optional user name
#          -P: optional password
#
# Jeff Hecker / Apogee 

select STDERR; $| = 1;
select STDOUT; $| = 1;

use DBI;

$U = "sa";
$P = "";
$DB = "";

while ( $ARGV[0] =~ m/^-/ ) {
  $_ = shift ( @ARGV );
  m/-U/ && do { $U = shift ( @ARGV ); };
  m/-P/ && do { $P = shift ( @ARGV ); };
  m/-D/ && do { $DB = shift ( @ARGV ); };
}

&usage unless ( $#ARGV >= 0 );

$WHITESPACE = "";

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
# Read in and digest the report definition file
#

open ( F, $ARGV[0] ) || die ( "Error opening " . $ARGV[0] );

@BEFORE = ();
@AFTER = ();
%CODE = ();
%LINECOUNT = ();
$C = "";
%page = (
	"LENGTH" , 66,
	"TOPMARGIN" , 3,
	"LEFTMARGIN" , 0,
	"RIGHTMARGIN" , 0,
	"BOTTOMMARGIN" , 3
	);


$MORE = "";
while ( <F> ) {
  chop;
  s/\s#.*//;
  next if ( m/^\s*$/ );

  if ( $MORE ) { $_ = $MORE . " " . $_ ;  $MORE = ""; }
  m/\\$/ && do {
    $MORE = $_ ;
    next;
  };

  m/^\s*database\s+([A-Za-z][0-9A-Za-z_]*)/i && do {
    $DB = $1;
    next;
  };

  m/\s*page\s+(.*)\s+([0-9]+)/i && do {
    if ( ! $C ) {
      $f = $1;
      $f =~ tr/a-z/A-Z/;
      $page { $f } = $2;
      next;
    }
  };

  m/^\s*(select\s+.*)/i && do {
    $q = $1;
    while ( <F> ) {
      chop;
      s/#.*//;
      $q .= " " . $_ ;
      last if m/;/ ;
    }
    $q =~ s/;// ;
    push ( @QUERIES, $q );
    next;
  }; # end - select

  m/^\s*before\s+first\s+row/ && do {
    $C = "before first";
    $WHITESPACE = "";
    next;
  }; # end - before first

  m/^\s*before\s+group\s+of\s+(.+)/ && do {
    $f = $1;
    $f =~ tr/a-z/A-Z/;
    $C = "BEFORE " . $f;
    $WHITESPACE = "";
    next;
  }; # end - before group

  m/^\s*on\s+every\s+row/ && do {
    $C = "every";
    $WHITESPACE = "";
    next;
  }; # end - every row

  m/^\s*after\s+group\s+of\s+(.+)/ && do {
    $f = $1;
    $f =~ tr/a-z/A-Z/;
    $C = "AFTER " . $f;
    $WHITESPACE = "";
    next;
  }; # end - after group

  m/^\s*after\s+last\s+row/ && do {
    $C = "after last";
    $WHITESPACE = "";
    next;
  }; # end - after last

  m/^\s*page\s+header/ && do {
    $C = "page header";
    $WHITESPACE = "";
    next;
  }; # end - page header

  m/^\s*page\s+footer/ && do {
    $C = "page footer";
    $WHITESPACE = "";
    next;
  }; # end - page header

  #
  # Not a "delimiter" so store it in the code cache...
  # Fisrt, do command line parameter substitutions...
  #

  if ( m/%[0-9]/ ) {
    # print "before: $_ \n";
    s/%([0-9]+)/\$ARGV[$1]/g;
    # print "after:  $_ \n";
  }

  #
  # change all prints to &prints...
  #

  m/(.*)(printf*)(.*)/ && do {
    $o = $1 . $2 . "(";
    @PRINT_WORDS = &shellwords ( $3 );
    while ( $x = shift ( @PRINT_WORDS ) {
      if ( $x =~ m/(.*);$/ ) {
        $x =~ s/;$/);/;
      }
    }
    if ( defined ( $LINE_COUNT { $C } )) {
      $LINE_COUNT { $C } += 1;
    } else {
      $LINE_COUNT { $C } = 1;
    }
  };

  $CODE { $C } .= $WHITESPACE . $_ ;
  $WHITESPACE = " ";

}
close F;

$q =~ tr/a-z/A-Z/;
$q =~s/\s*$//;

$w = ""; $o="";
if ( $q =~ m/select\s+(.+)\s+from\s+(.+)\s+where\s+(.+)\s+order\s+by\s+(.+)/i ) {
  $f = $1; $t = $2; $w = $3; $o = $4;
} elsif ( $q =~ m/select\s+(.+)\s+from\s+(.+)\s+order\s+by\s+(.+)/i ) {
  $f = $1; $t = $2;          $o = $3;
} elsif ( $q =~ m/select\s+(.+)\s+from\s+(.+)\s+group\s+by\s+(.+)/i ) {
  $f = $1; $t = $2;          $o = $3;
} elsif ( $q =~ m/select\s+(.+)\s+from\s+(.+)\s+where\s+(.+)\s+group\s+by\s+(.+)/i ) {
  $f = $1; $t = $2; $w = $3; $o = $4;
} elsif ( $q =~ m/select\s+(.+)\s+from\s+(.+)/i ) {
  $f = $1; $t = $2;
}
$f =~ s/\s+distinct\s+/ /i;
$o =~ s/\s+desc//i;

@FIELDNAME = ();
for $n ( split ( /\s*,\s*/, $f )) {
  $n =~ s/\s*=\s*.*//;				# SYBASE specific
  push ( @FIELDNAME, $n );
}

@ORDERBY = ();
for $f ( split ( /\s*,\s*/, $o )) {
  for $n ( 0 .. $#FIELDNAME ) {
    if ( $f eq $FIELDNAME [ $n ] ) {
      push ( @ORDERBY, $n );
      last;
    }
  }
  $n = $ORDERBY [ $#ORDERBY ];
  #
  # check for some before or after code...
  #
  if ( $CODE { "BEFORE " . $f } ) {
    $x = "sub before" . $n . "{" . $CODE { "BEFORE " . $f } . "}";
    eval $x;
  }
  if ( $CODE { "AFTER " . $f } ) {
    $x = "sub after" . $n . "{" . $CODE { "AFTER " . $f } . "}";
    eval $x;
  }
}

if ( $CODE { "before first" } ) {
  $x = "sub before_first {" . $CODE { "before first" } . "}";
  eval $x;
}

if ( $CODE { "every" } ) {
  $x = "sub every {" . $CODE { "every" } . "}";
  eval $x;
}

if ( $CODE { "after last" } ) {
  $x = "sub after_last {" . $CODE { "after last" } . "}";
  eval $x;
}

if ( $CODE { "page header" } ) {
  $x = "sub page_header {" . $CODE { "page header" } . "}";
  eval $x;
}

if ( $CODE { "page footer" } ) {
  $x = "sub page_footer {" . $CODE { "page footer" } . "}";
  eval $x;
}

$LINES_LEFT = $page { "LENGTH" };







# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
# Here we go.  Open $DB and start running queries
#


# $conn = DBI->connect ( "dbi:Sybase:database=" . $DB . ":scriptname=sy_report", $U, $P );
$conn = DBI->connect ( "dbi:Sybase:scriptname=sy_report", $U, $P );
$conn->do ( "use $DB" );

# all initial queries will be into temp tables.  We only
# need to deal with the last one...

for $q ( 0 .. $#QUERIES ) {
  $cursor = $conn->prepare ( $QUERIES [ $q ] );
  $cursor->execute;
  if ( $q < $#QUERIES ) {
    $cursor->finish();
  }
}

$MORE = 1;
$MORE = 0  unless ( @DATA = $cursor->fetchrow_array );
@PREVDATA = ();
$ROW = 0;
$PAGE = 1;

#
# do first page header now
#

&page_header() if defined ( &page_header );

#
# do before first row now
#

&before_first() if defined ( &before_first );

while ( $MORE ) {
  #
  # check all befores...
  #
  for $n ( @ORDERBY ) {
    if (( ! defined ( $PREVDATA [ $n ] )) || ( $PREVDATA [ $n ] ne $DATA [ $n ] )) {
      $x = "before" . $n;
      if ( defined ( &$x )) {
        &$x() ;
      }
    }
  }

  ++$ROW;
  #
  # every row...
  #
  &every() if defined ( &every );

  $MORE = 0  unless ( @NEWDATA = $cursor->fetchrow_array );
  #
  # check all the afters here...
  #
  for $n ( reverse @ORDERBY ) {
    if (( ! defined ( $NEWDATA [ $n ] )) || ( $NEWDATA [ $n ] ne $DATA [ $n ] )) {
      $x = "after" . $n;
      if ( defined ( &$x )) {
        &$x() ;
      }
    }
  }

  @PREVDATA = @DATA;
  @DATA = @NEWDATA;
  @NEWDATA = ();

}

#
# do after last row here
#
&after_last()  if defined ( &after_last );


$conn->disconnect;
exit;

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

sub need {
  local $N = @_;

  if ( $N > $LINES_LEFT ) {
    while ( $LINES_LEFT-- ) {
      print "\n";
    }
    &page_footer() if defined ( &page_footer );
    $LINES_LEFT = $page { "LENGTH" };
    &page_header() if defined ( &page_header );
  }
}


sub print {
  local @P = @_ ;

  &need  ( 1 );
  print ( @P );
}


sub printf {
  local @P = @_ ;

  &need  ( 1 );
  printf ( @P );
}

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

sub usage {

print STDERR <<EOF;
 sy_report [options] report_definition_file [parameters] [parameter...]

 options:
         -D: optional database name
         -U: optional user name
         -P: optional password

 Jeff Hecker / Apogee 

EOF

exit;

}