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

# Show a MySQL Table
# Table and DB  name specified via Querystring.
# April 2007, by Vijay Anand


use strict;
use CGI qw/:standard *table start_ul/;
use DBI;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);

my $db    = param ("DB") || "hardware";
my $table = param ("TABLE") || "subnet";
my $row_limit = param("ROWLIMIT") || 400;

my $dbh = DBI->connect("DBI:mysql:database=$db;host=localhost","root","**YOUR PASSWORD**")
      or die "Could not connect to database $db (credentials?)\n";



my $returnFields=param("return") || "*";

#parse through criteria parameters

my @allcriteria=();
my @fieldnames;
##my @allparams=param();

foreach my $param ( param() ) {
	next unless $param=~/criteria_condition_(.*)/;
	push @fieldnames, $1;
        next if param($param) eq "0"; # Ignore zero criteria
        next unless param("criteria_value_$1");   #ignore blank criteria
	push @allcriteria, $1." ".param($param)." ".param("criteria_value_$1");
}

my $queryCriteria=join(" ".param("criteria_logic")." ",@allcriteria);


my $sqlstatement="select $returnFields from $table";
if ($queryCriteria) {
  	$sqlstatement.=" where ($queryCriteria)"
}

param("criteria_grouping") and $sqlstatement .= " " . param("criteria_grouping");

Print_Header_And_Styles();



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

$sth->execute();



{
  my @fieldnames_new = Display_Rows();
  @fieldnames=@fieldnames_new if @fieldnames_new;
}

$sth->finish();
$dbh->disconnect;

Print_criteria_customization_form(@fieldnames);
print end_html;


#------------------------------------------------


sub Display_Rows{
   my $row = 0;
   my @fieldnames;
   my @ToggleClass = ( {-class=>"coloredrow"}, {-class=>"treedetailbg"});
   print start_table({-border=>undef, -align=>'LEFT'}), #  , -width=>'100%'
        Tr(
           td({-bgcolor=>'LIGHTGREEN'}, a({-href=>url(-base=>1) }, b("HOME"))  ),
           td( $sth->rows . " rows available"),
           td("DB=$db"),
           td("Table=$table"),
           td($queryCriteria || "*"),
           td ({-bgcolor=>'LIGHTGREEN' }," ")
           ),
         end_table, "\n",

         br({clear=>'left'}) # Re-flow page to the LEFT.
        #p({-align=>'LEFT'},"  \n"),  # This piece is required, to fix rendering problems
   ;

   return if $sth->rows < 1;  #SOMETIMES, WE GET NEGATIVE ONE
   print  start_table({-border=>1,  -cellpadding=>"1",      # , -width=>'100%'
                -cellspacing=>"1", -align=>'LEFT'});
   while( my $rec = $sth->fetchrow_hashref()) {
      # Print header, if this is the first row
      ++$row == 1 and print Tr(
               map {my $h=$_;$h=~s/_/ /g; td({-class=>"treedetailheader"}
                    ,b($h))} @fieldnames = keys(%$rec)
              ), "\n";
      last if ($row > $row_limit);
      # Print field data..
      print Tr(   $ToggleClass[$row % 2]  ,
               map {td(  escapeHTML($rec->{$_}) )} keys %$rec
              ), "\n";

    }
    print end_table,"\n";
    #Does not help: print p({-align=>'LEFT'},'&nbsp;  '); # This piece is required, to fix rendering problems
    $row > $row_limit and print h4 ("--- Truncated to $row_limit Rows ---");
    return @fieldnames;
}

#------------------------------------------------

sub Print_criteria_customization_form{
    my @fieldNames = @_;
    print          br({clear=>'left'}) ;# Re-flow page to the LEFT.
    my  $tableHTML="<FORM method='get' action='". url(-relative) . "'>\n";
    $tableHTML .= "<TABLE  border=0 bgcolor='lightblue'>\n<TR><TD colspan=3>" .
                "<FONT face=\"Arial, Helvetica, sans-serif\" size=\"-1\" color=\"navy\">Customize the table view using the fields below.".
                "<br>You must put single quotes around text values.".
                "<br>Click Apply with all set to IGNORE to view whole table.</FONT></TD></TR>\n";

    foreach my $fieldName (@fieldNames) {
    	$tableHTML.="<TR><TD>$fieldName</TD><TD>";
    	$tableHTML.="<select name=\"criteria_condition_$fieldName\"><option value=\"0\">IGNORE</option>".
    	            "<option value=\"=\">equal to</option>".
                        "<option value=\"like\">like</option>".
                        "<option value=\"&gt;=\">greater/equal to</option>".
                        "<option value=\"&lt;=\">less/equal to</option>".
                        "<option value=\"&gt;\">greater than</option>".
                        "<option value=\"&lt;\">less than</option></select></TD>";
        $tableHTML.="<TD><INPUT type=text width=10 name=\"criteria_value_$fieldName\"></TD></TR>\n";
    }

    $tableHTML.="<TR><TD colspan=3>Combine criteria with <select name=\"criteria_logic\">\n".
                "<option value=\"AND\">AND</option><option value=\"OR\">OR</option></select></TD></TR>\n";
    $tableHTML.="</TABLE>" .
                "<INPUT type=hidden name=\"TABLE\" value=$table>\n".
                "<INPUT type=hidden name=\"DB\" value=$db>\n".
                "<INPUT type=hidden name=\"return\" value=$returnFields>".
                "<INPUT type=submit value=\"Apply Criteria\"></FORM>".
                "";

    print $tableHTML;

}



#------------------------------------------------

sub Print_Header_And_Styles{

print header;

print start_html("CGI DBI $db $table");

print <<'STYLESHEET';

  <style type="text/css">
        TABLE.spacecolumns TD { padding-right: 4pt }
        TABLE.spacecolumns TH { padding-right: 4pt }

        body {
        	margin-top: 5px;
        	margin-left: 5px;
        	margin-right: 5px;
         	margin-bottom: 5px;
        	font-family: Arial, Helvetica, sans-serif;
        	font-size: 11px;
        	background: #FFFFFF;
        	color: #333333;
        	scrollbar-arrow-color: #FFFFFF;
        	scrollbar-base-color: #CCCCCC;
        	scrollbar-darkshadow-color: #FFFFFF;
        	scrollbar-3dlight-color: #FFFFFF;
        	scrollbar-track-color: #CCCCCC;
        	scrollbar-face-color: #24486A;
        	scrollbar-shadow-color: #24486A;
        	scrollbar-highlight-color: #24486A;
        }



        p {

       	font-family: Arial, Helvetica, sans-serif;
        	font-size: 10px;
        	background: #FFFFFF;
        	color: #333333;
        }



        td {

       	font-family: Arial, Helvetica, sans-serif;
        	font-size:10px;
        	color: #333333;
        }



        .treedetailheader {
       	background: #C9E1F7;
        	color: #333333;

        }



        .coloredrow {
       	background: #F0F0F0;
       	color: #333333;
        }

        .treedetailbg {
       	background: #FFFFFF;
       	color: #333333;
        }
    </style>

STYLESHEET

}
#------------------------------------------------