#!/usr/bin/perl #GUFE: Generic but Usable Front End #by Aaron Weiss (aaron@pobox.com) for the Web Developers' Virtual Library #http://www.wdvl.com #You are encouraged to learn from, adapt, modify, and otherwise customize this script to your minds' content. use CGI; use DBI; #setup database parameters my $DBD="ODBC"; my $dbase="Clients"; my $dbuser=""; my $dbpassword=""; #prepare for output to browser $cgiobj=new CGI; print $cgiobj->header; #retrieve CGI parameters my $returnFields=$cgiobj->param("return"); unless ($returnFields) { $returnFields="*" } my $queryTable=$cgiobj->param("table"); my $querySort=$cgiobj->param("sort"); my $querySortType=$cgiobj->param("sortType"); #parse through criteria parameters my @allcriteria=(); my @allparams=$cgiobj->param(); foreach $param (@allparams) { if (($param=~/criteria_condition_(.*)/)&&($cgiobj->param($param) ne "0")) { my $criterion=$1." ".$cgiobj->param($param)." ".$cgiobj->param("criteria_value_$1"); if ($cgiobj->param("criteria_value_$1")) { #ignore blank criteria push (@allcriteria,$criterion); } } } $queryCriteria=join(" ".$cgiobj->param("criteria_logic")." ",@allcriteria); #mission control ... set the balls in motion my $dbh=&openDB($DBD,$dbase,$dbuser,$dbpassword); if ($queryTable) { my $sqlstatement="select $returnFields from $queryTable"; if ($queryCriteria) { $sqlstatement.=" where ($queryCriteria)" } if ($querySort) { $sqlstatement.=" order by $querySort $querySortType" } my $sth=&sendSQL($sqlstatement,$dbh); print &createPage(&resultTable($dbh,$sth,$queryTable,$returnFields)); } else { #no table has been selected to view print &createPage(&resultTable($dbh)); } sub openDB { #open connection to database my ($DBD,$dbase,$dbuser,$dbpassword)=@_; my $dbh = DBI->connect("dbi:$DBD:$dbase",$dbuser,$dbpassword); return $dbh; } #end openDB sub sendSQL { #send SQL query my ($sqlstatement,$dbh)=@_; my $sth = $dbh->prepare($sqlstatement); $sth->execute || &sqlAbort("Could not execute SQL statement ... maybe invalid?
Here is the statment that choked: $sqlstatement"); return $sth; }#end sendSQL sub resultTable { #output SQL statement results into an HTML table my $rowcount=0; my ($dbh,$sth,$queryTable,$returnFields)=@_; my $tableHTML=""; my $labelrow=""; my @fieldNames=(); my $fieldCount=0; #reconstruct the query URL to pass on via hyperlinks my $queryURLa="table=$queryTable&return=$returnFields"; if ($queryCriteria) { my @allparams=$cgiobj->param(); foreach my $param (@allparams) { if ($param=~/criteria/) { $queryURLa.="&$param=".$cgiobj->param($param) } } } if ($queryTable){ while (my $row=$sth->fetchrow_hashref) { #retrieve each record row-by-row from the database #build HTML table row-by-row using this data $tableHTML.=""; @fieldNames=keys %$row; $fieldCount=$#fieldNames+1; foreach my $fieldName (@fieldNames) { unless ($rowcount>0) { my $queryURLb=$queryURLa."&sort=$fieldName"; if ($querySortType eq "ASC") { $queryURLb.="&sortType=DESC" } else { $queryURLb.="&sortType=ASC" } #wrap hyperlink around field names, rigged with sort parameters $labelrow.="$fieldName"; } if ($fieldName eq "Invoice") { #wrap hyperlink around invoice data, rigged to invoice table $tableHTML.="{$fieldName}\">$row->{$fieldName}"; } else { $tableHTML.="$row->{$fieldName}"; } } $tableHTML.=""; $rowcount++; } if ($rowcount==0) { #database did not return any records $tableHTML.="There is no table view that matches your selected criteria."; } } #end if wrapper else { #no table has been selected to view $queryTable="None Selected"; } #generate hyperlinks to other tables in this database @tableNames=$dbh->tables; $tableHTML.="
Available tables to view: "; foreach $tableName (@tableNames) { unless (($tableName eq $queryTable)&&($rowcount>0)) { $tableHTML.="$tableName "; } } $tableHTML.=""; $tableHTML="$labelrow$tableHTML
Table: $queryTable
"; unless (($queryTable eq "None Selected")||($rowcount==0)) { #construct criteria customization form $tableHTML.="

Customize the table view using the fields below.". "
You must put single quotes around text values.". "
Click Apply with all set to IGNORE to view whole table.

"; $tableHTML.="
"; $tableHTML.=""; foreach $fieldName (@fieldNames) { $tableHTML.=""; $tableHTML.=""; } $tableHTML.=""; $tableHTML.="
$fieldName"; $tableHTML.="
Combine criteria with
". "". "
"; }#end unless wrapper return $tableHTML } #end resultTable sub createPage { #insert the table into the template page #the HTML template page must contain the comment where you wish GUFE's output to appear my ($tableHTML)=@_; my $templatePage="gufe.html"; #full filesystem path to template HTML page my $finalPage=""; open (INFILE,"$templatePage"); my @template=; close (INFILE); $finalPage=join('',@template); $finalPage=~s//$tableHTML/; return $finalPage } #end createPage