# ResultSet.pm - Will pull settings from an XML config file into a format usable by the system.
# Created by James A. Pattie.  Copyright (c) 2001-2002, Xperience, Inc.

package DBIWrapper::ResultSet;

use strict;
use XML::LibXML;
use vars qw ($AUTOLOAD @ISA @EXPORT $VERSION);

require Exporter;
@ISA = qw(Exporter AutoLoader);
@EXPORT = qw();

$VERSION = "1.1";

# new
sub new
{
  my $that = shift;
  my $class = ref($that) || $that;
  my $self = bless {}, $class;
  my $errStr = "ResultSet->new()  - Error:";

  $self->{version} = "1.2";
  $self->{resultFile} = "";
  $self->{select} = 0;  # indicates if a <select /> tag was in the xml.
  $self->{sql} = "";
  $self->{plugs} = "";
  $self->{result} = "";
  $self->{error} = "";
  $self->{columns} = "0";
  $self->{numRows} = "0";
  $self->{rows} = [];
  $self->{columnNames} = [];  # the column names used.
  $self->{columnNamesHash} = {};  # same as columnNames but for quicker lookups.
  $self->{errorCodes} = {
    0  => "version = '%s' is invalid",
    1  => "resultFile must be specified",
    2  => "sql must be specified",
    3  => "result = '%s' is invalid",
    4  => "columns = '%s' is invalid",
    5  => "numRows = '%s' is invalid",
    6  => "numRows = '%s' but rows = '%s'!",
    7  => "no columnNames defined",
    8  => "columnNames count != columnNamesHash",
    9  => "column = '%s' in columnNamesHash, not in columnNames",
    10 => "row = '%s' only has '%s' columns defined",
    11 => "row = '%s', column = '%s' defined but not in columnNames!",
  };

  return $self;
}

sub AUTOLOAD
{
  my $self = shift;
  my $type = ref($self) || die "$self is not an object";
  my $name = $AUTOLOAD;
  $name =~ s/.*://; # strip fully-qualified portion
  unless (exists $self->{$name})
  {
    die "Can't access `$name' field in object of class $type";
  }
  if (@_)
  {
    return $self->{$name} = shift;
  }
  else
  {
    return $self->{$name};
  }
}

sub isValid
{
  my $self = shift;
  my @errors = ();

  my $strict = 0;

  if ($self->{version} !~ /^(1.2)$/)
  {
    push @errors, sprintf($self->{errorCodes}->{0}, $self->{version});
  }
  if (length $self->{resultFile} == 0)
  {
    push @errors, $self->{errorCodes}->{1};
  }
  if (length $self->{sql} == 0)
  {
    push @errors, $self->{errorCodes}->{2};
  }
  if ($self->{result} !~ /^(Ok|Error)$/)
  {
    push @errors, sprintf($self->{errorCodes}->{3}, $self->{result});
  }
  if ($self->{columns} !~ /^(0|1)$/)
  {
    push @errors, sprintf($self->{errorCodes}->{4}, $self->{columns});
  }
  if ($self->{numRows} !~ /^(\d+)$/)
  {
    push @errors, sprintf($self->{errorCodes}->{5}, $self->{numRows});
  }
  else
  {
    # validate that we have the expected number of rows.
    if (scalar @{$self->{rows}} != $self->{numRows})
    {
      push @errors, sprintf($self->{errorCodes}->{6}, $self->{numRows}, scalar @{$self->{rows}});
    }
    if ($self->{numRows} > 0)
    {
      my $numColumns = scalar @{$self->{columnNames}};
      if ($numColumns == 0)
      {
        push @errors, $self->{errorCodes}->{7};
      }
      else
      {
        if ($numColumns != scalar keys %{$self->{columnNamesHash}})
        {
          push @errors, $self->{errorCodes}->{8};
        }
        # now verify that all entries in the columnNamesHash exist in columnNames
        foreach my $column (keys %{$self->{columnNamesHash}})
        {
          my $found = 0;
          for (my $k=0; $k < scalar @{$self->{columnNames}} && !$found; $k++)
          {
            if ($self->{columnNames}->[$k] == $column)
            {
              $found = 1;
            }
          }
          if (!$found)
          {
            push @errors, sprintf($self->{errorCodes}->{9}, $column);
          }
        }
        # make sure each row has the correct number of columns defined.
        for (my $i=0; $i < scalar @{$self->{rows}}; $i++)
        {
          my $rowRef = $self->{rows}->[$i];
          if (scalar(keys %{$rowRef}) != $numColumns)
          {
            push @errors, sprintf($self->{errorCodes}->{10}, $i, scalar(keys %{$rowRef}));
          }
          else  # validate that all columns defined in the row exist in columnNames
          {
            foreach my $columnName (keys %{$self->{rows}->[$i]})
            {
              if (not exists $self->{columnNamesHash}->{$columnName})
              {
                push @errors, sprintf($self->{errorCodes}->{11}, $i, $columnName);
              }
            }
          }
        }
      }
    }
  }

  return ((scalar @errors > 0 ? 0 : 1), \@errors);
}

sub displayData
{
  my $self = shift;
  my %args = ( style => "text", @_ );
  my $style = $args{style};
  my $result = "";
  my $errStr = "ResultSet->displayData()  - Error:";

  if ($style !~ /^(text|html)$/)
  {
    die "$errStr  style = '$style' is invalid!\n";
  }

  my @valid = $self->isValid();
  if ($valid[0])
  {
    my $columnNames = "'" . join ("', '", @{$self->{columnNames}}) . "'";
    if ($style eq "text")
    {
      $result .= "version = '$self->{version}'\n";
      $result .= "resultFile = '$self->{resultFile}'\n";
      $result .= "sql = '$self->{sql}'\n";
      $result .= "plug = '$self->{plug}'\n";
      $result .= "result = '$self->{result}'\n";
      $result .= "error = '$self->{error}'\n";
      $result .= "columns = '$self->{columns}'\n";
      $result .= "numRows = '$self->{numRows}'\n";
      $result .= "columnNames = '$columnNames'\n";
      for (my $i=0; $i < $self->{numRows}; $i++)
      {
        $result .= "row $i: ";
        for (my $j=0; $j < scalar @{$self->{columnNames}}; $j++)
        {
          my $column = $self->{columnNames}->[$j];
          $result .= ", " if ($j > 0);
          $result .= "'$column' = '$self->{rows}->[$i]->{$column}'";
        }
      }
    }
    elsif ($style eq "html")
    {
      my $resultFile = $self->encodeHTML(string => $self->{resultFile});

      my $error = $self->encodeHTML(string => $self->{error});
      my $sql = $self->encodeHTML(string => $self->{sql});
      my $plug = $self->encodeHTML(string => $self->{plug});

      $result .= <<"END_OF_CODE";

<center><h1>ResultSet Version $self->{version}</h1></center>
  <table border="1" cellpadding="2" cellspacing="0" width="100%">
    <tr>
      <td colspan="2" bgcolor="yellow"><b>Info</b></td>
    </tr>
    <tr>
      <td colspan="2">
        <table border="1" cellpadding="2" cellspacing="0" width="100%">
          <tr>
            <td valign="top" align="right"><b>resultFile</b></td>
            <td bgcolor="cyan">'$resultFile'</td>
          </tr>
          <tr>
            <td valign="top" align="right"><b>sql</b></td>
            <td bgcolor="cyan">'$sql'</td>
          </tr>
          <tr>
            <td valign="top" align="right"><b>plug</b></td>
            <td bgcolor="cyan">'$plug'</td>
          </tr>
          <tr>
            <td valign="top" align="right"><b>result</b></td>
            <td bgcolor="cyan">'$self->{result}'</td>
          </tr>
          <tr>
            <td valign="top" align="right"><b>error</b></td>
            <td bgcolor="cyan">'$error'</td>
          </tr>
          <tr>
            <td valign="top" align="right"><b>columns</b></td>
            <td bgcolor="cyan">'$self->{columns}'</td>
          </tr>
          <tr>
            <td valign="top" align="right"><b>numRows</b></td>
            <td bgcolor="cyan">'$self->{numRows}'</td>
          </tr>
          <tr>
            <td valign="top" align="right"><b>columnNames</b></td>
            <td bgcolor="cyan">'$columnNames'</td>
          </tr>
        </table>
      </td>
    </tr>
    <tr>
      <td colspan="2" bgcolor="yellow"><b>rows</b></td>
    </tr>
    <tr>
      <td colspan="2">
        <table border="0" cellpadding="0" cellspacing="0" width="100%">
          <tr>
            <td width="25%">&nbsp;</td>
            <td width="75%">
              <table border="1" cellpadding="2" cellspacing="0" width="100%">
END_OF_CODE
      for (my $index=0; $index < $self->{numRows}; $index++)
      {
        $result .= <<"END_OF_CODE";
                <tr>
                  <td align="right" width="25%"><b><font color="red">$index</font></b></td>
                  <td bgcolor="cyan" width="75%">&nbsp;</td>
                </tr>
                <tr>
                  <td colspan="2">
                    <table border="0" cellpadding="0" cellspacing="0" width="100%">
                      <tr>
                        <td width="25%">&nbsp;</td>
                        <td width="75%">
                          <table border="1" cellpadding="2" cellspacing="0" width="100%">
END_OF_CODE
        foreach my $column (@{$self->{columnNames}})
        {
          my $value = $self->encodeHTML(string => $self->{rows}->[$index]->{$column}, double => 1);
          $result .= <<"END_OF_CODE";
                            <tr>
                              <td align="right"><b>$column</b></td>
                              <td bgcolor="cyan">'$value'</td>
                            </tr>
END_OF_CODE
        }
        $result .= <<"END_OF_CODE";
                          </table>
                        </td>
                      </tr>
                    </table>
                  </td>
                </tr>
END_OF_CODE
      }
      $result .= <<"END_OF_CODE";
              </table>
            </td>
          </tr>
        </table>
      </td>
    </tr>
  </table>
END_OF_CODE
    }
  }
  else
  {
    if ($style eq "text")
    {
      $result .= "Config File not valid!\n\n";
      $result .= join("\n", @{$valid[1]}) . "\n";
    }
    elsif ($style eq "html")
    {
      $result .= "Config File not valid!<br>\n<br>\n";
      $result .= join("<br>\n", @{$valid[1]}) . "<br>\n";
    }
    die $result;
  }

  return $result;
}

sub encodeHTML
{
  my $self = shift;
  my %args = ( string => "", double => 0, @_ );
  my $string = $args{string};
  my $double = $args{double};

  $string =~ s/&/&amp;/g;
  $string =~ s/</&lt;/g;
  $string =~ s/>/&gt;/g;
  if ($double)
  {
    $string =~ s/\\n/<br>\n/g;
  }
  else
  {
    $string =~ s/\n/<br>\n/g;
  }
  $string =~ s/  /&nbsp;&nbsp;/g;

  return $string;
}

sub generateXML
{
  my $self = shift;
  my $result = "";
  my $errStr = "ResultSet->generateXML()  - Error:";

  my @valid = $self->isValid();
  if ($valid[0])
  {
    my $sql = $self->encodeEntities(string => $self->{sql});
    my $plug = $self->encodeEntities(string => $self->{plug});
    my $error = $self->encodeEntities(string => $self->{error});
    $result .= <<"END_OF_XML";
<?xml version="1.0" encoding="ISO-8859-1"?>
<resultset version="$self->{version}">
END_OF_XML
    $result .= qq(  <select sql="$sql" plug="$plug"/>\n) if ($self->{select});
    $result .= <<"END_OF_XML";
  <status result="$self->{result}" error="$error"/>
  <rows numRows="$self->{numRows}" columns="$self->{columns}">
END_OF_XML
    for (my $i=0; $i < $self->{numRows}; $i++)
    {
      if ($self->{columns})
      {
        $result .= "    <row>\n";
        foreach my $column (@{$self->{columnNames}})
        {
          my $data = $self->{rows}->[$i]->{$column};
          my $value = $self->encodeEntities(string => $data);
          my $name = $self->fixupAttributes($column);
          if ($self->{columns} == 1)
          {
            $result .= "      <column name=\"$name\" value=\"$value\"/>\n";
          }
          elsif ($self->{columns} == 2)
          {
            $result .= "      <$name><![CDATA[$data]]></$name>\n";
          }
        }
        $result .= "    </row>\n";
      }
      else
      {
        $result .= "    <row";
        foreach my $column (@{$self->{columnNames}})
        {
          my $value = $self->encodeEntities(string => $self->{rows}->[$i]->{$column});
          my $name = $self->fixupAttributes($column);
          $result .= " $name=\"$value\"";
        }
        $result .= "/>\n";
      }
    }
    $result .= <<"END_OF_XML";
  </rows>
</resultset>
END_OF_XML
  }
  else
  {
    $result .= "ResultSet not valid!\n\n";
    $result .= join("\n", @{$valid[1]}) . "\n";
    die $result;
  }

  return $result;
}

# string encodeEntities(string)
# requires: string - string to encode
# optional:
# returns: string that has been encoded.
# summary: replaces all special characters with their XML entity equivalent. " => &quot;
sub encodeEntities
{
  my $self = shift;
  my %args = ( string => "", @_ );
  my $string = $args{string};

  my @entities = ('&', '"', '<', '>', '\n');
  my %entities = ('&' => '&amp;', '"' => '&quot;', '<' => '&lt;', '>' => '&gt;', '\n' => '\\n');

  return $string if (length $string == 0);

  foreach my $entity (@entities)
  {
    $string =~ s/$entity/$entities{$entity}/g;
  }

  return $string;
}

=item scalar fixupAttributes(string)

Attempts to make sure that the given string can be a valid attribute in
an xml document.

Converts (, ), -, \, /, =, >, <, & to _
Deletes ', ", \n

=cut
sub fixupAttributes
{
  my $self = shift;
  my $string = "";
  if (scalar @_ == 1)
  {
    $string = shift;
  }
  else
  {
    my %args = ( string => "", @_ );
    $string = $args{string};
  }

  my @replace = ('&', '<', '>', '-', '\\(', '\\)', '\\\\', '/', '=');
  my @delete = ("'", '"', '\n');

  return $string if (length $string == 0);

  foreach my $entity (@replace)
  {
    $string =~ s/$entity/_/g;
  }

  foreach my $entity (@delete)
  {
    $string =~ s/$entity//g;
  }

  return $string;
}

1;
__END__

=head1 NAME

ResultSet - The XML Database ResultSet Module.

=head1 SYNOPSIS

  use DBIWrapper::ResultSet;
  my $obj = DBIWrapper::ResultSet->new();

=head1 DESCRIPTION

ResultSet will contain the parsed XML file generated by readXML().
It provides a method to validate that it is complete and also a method
to generate a valid XML file from the data stored in the data hash.

=head1 FUNCTIONS

  scalar new()
    Creates a new instance of the DBIWrapper::ResultSet
    object.

  array isValid(void)
    Determines if the data structure is complete and usable for
    generating an XML file from.
    Returns an array.  The first index is boolean (1 or 0 to indicate
    if the object is valid or not).  The second index is an array of
    error messages that were generated based upon the errors found.

  string displayData(style)
    Outputs the contents of the perl data structure after validating
    it.  If style = 'text' then formats for the console, else if
    style = 'html' we generate valid html output and format in a table.
    The content is suitable to place straight into the <body> section
    of your HTML page.

  string generateXML(void)
    Creates an XML file based upon the info stored in the ResultSet.
    It first calls isValid() to make sure this is possible.  If
    not then we die with an informative error message.

=head1 VARIABLES

  version - version of the XML file parsed

  resultFile - the name of the file parsed or the string of XML

  sql - the SELECT statement issued which generated the XML

  plug - the options that were plugged into the SQL statement

  result - Ok or Error.  Indicates the status returned by the database

  error - The error string returned if result = Error

  numRows - the number of rows returned from the database

  columns - 0 or 1.  Indicates if we had <column> children for each <row>
            or if the data returned was stored by the name given from the
            database in the <row> with no children tags.

  rows - the array of row entries returned.  Each row is a hash of
         column name = column value attributes.

  columnNames - The names of the columns used in the XML file.

  errorCodes - hash of error codes to messages returned by isValid().

  NOTE:  All data fields are accessible by specifying the object
         and pointing to the data member to be modified on the
         left-hand side of the assignment.
         Ex.  $obj->variable($newValue); or $value = $obj->variable;

=head1 AUTHOR

Xperience, Inc. (mailto:admin at pcxperience.com)

=head1 SEE ALSO

perl(1), DBIWrapper(3)

=cut