#!c:/perl/bin/perl.exe

use strict;

use DBI;
use CGI;



my $DB_Host = 'localhost';
my $DB_Name = 'proyecto';
my $DB_User = 'root';
my $DB_Pass = 'xyz123';



{  # begin main



  #--
  # Create a new CGI object to process the HTML form.

  my $cgi = new CGI;



  #--
  # Get the value of the "Patente" field from our HTML input form.

  my $PatenteToQuery = $cgi->param('Patente');
  my $PedimentoToQuery = $cgi->param('Pedimento');


  #--
  # Get a connection to the database server, aborting on failure.

  my $dbh = openDBConnection($DB_Host, $DB_Name, $DB_User, $DB_Pass);

  if ( ! defined $dbh ) {
    printErrorPage("Could not open a connection to the database!");
    exit;
  }



  #--
  # Define the query, prepare it, execute it then get the results as an array of hash references.

  my $sql = "SELECT * FROM final WHERE pat = \"" . $PatenteToQuery . "\"" and WHERE ped = \"" . $PedimentoToQuery ."\";

  my $sth = $dbh->prepare($sql);

  $sth->execute();

  my $queryResults = $sth->fetchall_arrayref({});



  #--
  # Print the results as an HTML table.
  #
  # Note: the {'Last'} and {'First'} found below refer to column names in the database table.

  print STDOUT "Content-Type: text/html\n";
  print STDOUT "\n";

  print STDOUT "<TABLE BORDER=\"1\">" . "\n";

  print STDOUT "<TR><TH>Patente</TH><TH>IMP/EXP</TH><TH>PEDIMENTO</TH><TH>PED. ORIGINAL</TH><TH>CLAVE PEDIMENTO</TH><TH>FECHA</TH><TH>RFCAAA</TH><TH>CURPAA</TH><TH>PESOB</TH><TH>SUMA</TH><TH>BANCO</TH><TH>ARCHIVO</TH></TR>" . "\n";

  foreach my $i (0 .. $#$queryResults) {
    print STDOUT "<TR><TD>" . $queryResults->[$i]->{'pat'} . "</TD><TD>" . $queryResults->[$i]->{'impexp'} . "</TD></TD>" .   "</TD><TD>" . $queryResults->[$i]->{'ped'} . "</TD><TD>" . $queryResults->[$i]->{'pedorig'} . "</TD><TD>" . $queryResults->[$i]->{'cveped'} . "</TD><TD>" . $queryResults->[$i]->{'fecha'} . "</TD><TD>" . $queryResults->[$i]->{'rfcaaa'} . "</TD><TD>" . $queryResults->[$i]->{'curpaa'} . "</TD><TD>" . $queryResults->[$i]->{'pesob'} . "</TD><TD>" . $queryResults->[$i]->{'suma'} . "</TD><TD>" . $queryResults->[$i]->{'nombrebanco'} . "</TD><TD>" . $queryResults->[$i]->{'archivo'} ."\n";
  }

  print STDOUT "</TABLE>" . "\n";
  


  #--
  # Close our database connection.

  $dbh->disconnect();



}  # end main



# =========================================================================
#              F U N C T I O N    D E F I N I T I O N S
# =========================================================================



# -------------------------------------------------------------------------
# |
# | Function  : printErrorPage
# |
# | Parameter : $errorMessage = the error message to print
# |
# | Return    : none
# |
# | Comments  : Prints an HTML error message.  This prints the "Content-Type"
# |             HTTP header.
# |
# -------------------------------------------------------------------------

sub printErrorPage
{
  my $errorMessage = shift;

  print STDOUT "Content-Type: text/html\n";
  print STDOUT "\n";

  print STDOUT "<H1>" . $errorMessage. "</H1>\n";

}



# -------------------------------------------------------------------------
# |
# | Function  : openDBConnection
# |
# | Parameter : $host     = the name or ip of the database server
# | Parameter : $db       = the name of the database to connect to
# | Parameter : $username = the username to use when connecting to the MySQL server
# | Parameter : $password = the password to use when connecting to the MySQL server
# |
# | Return    : A handle to the database connection.
# |
# | Comments  :
# |
# -------------------------------------------------------------------------

sub openDBConnection
{
  my $host     = shift;
  my $db       = shift;
  my $username = shift;
  my $password = shift;

  my $dbh;  # Handle to a database connections.

  $dbh = DBI->connect("DBI:mysql:$db:$host",
                      $username, $password,
                      { RaiseError => 0, AutoCommit => 1 }
                     );

  if ( ! defined ($dbh) ) {
    return undef;
  }
  else {
    return $dbh;
  }

}