$cginame = "CGI4";
$cgifolder = "";
$pageURL = "../index.htm";
$cgiurl = "\""."$cgifolder"."$cginame".".pl"."\"";
$template = "Template1";
###############################################################
#             1.Environmental prefix                          #
###############################################################
#                  *-OS platform -*                           #
$osplat = "Win32";
#             *-DB connectivity platform-*                    #
$dbplat = "ODBC";
###############################################################
#             2.Section of datasource properies               #
###############################################################
$dbname = "ADDBOOK";
###############################################################
#              3.Section of columns                           #
###############################################################
$command = SELECT;
@columns = ( 'FullName','Country','City','Address','EmailAddress','TuitionPlan','Date');
@captions = ( 'Name','Country','City','Address','Email Address','Tuition Plan','Visit date');
@columntype = ( 'NONE','NONE','NONE','NONE','MAIL','NONE','DATE_0');
###############################################################
#              4.Section of controls                          #
###############################################################
@controls = ();
@controlvalues = ();
&get_control_values(@controls);
###############################################################
#        5.Section of script body where    generated SQL      #
#       statement template is to be placed with appropriate   #
#                        insertions                           #
###############################################################
$stmt0 = "SELECT Addresses.FullName,Addresses.Country,Addresses.City,Addresses.Address,Addresses.EmailAddress,Addresses.TuitionPlan,Addresses.Date\n
 FROM Addresses\n
ORDER BY  Date ASC";
   ###############################################################
   #              6.Section of feedback properies                #
   ###############################################################
@colors = ("d3d3d3","ffffff");
$quota = 3;
$style = 1;#0-List,1-Table
$border = 1;
$link = 0;#0-Link,1-Embed
$head1 = "GuestBook Visitors List";  #first line
$head2 = "";  #second line
$align = "center";
##################### End of prefix ###########################

#$test = FALSE;
#####################Standard remainder of the script##########
$useplatstr = "use "."$osplat"."::"."$dbplat";
eval $useplatstr;
use CGI ':standard';
$header = "Header";

#################################################################
#        7.Section of script body responsible                   #
#             for input from stdio                              #
#        and output to stdio footer's lines                     #
#################################################################
print header;
#print   "<center><U><B><FONT SIZE=\"+5\">SQL TEST</FONT></B></U><center>";
if (param()) {
 }

#################################################################
#        8.Section of script body responsible                   #
#           for placing controls' values                        #
#      in appropriate places in "controlvalues" array           #
#      and placing received cursor in $cursor variable          #
#################################################################
$cursor = param('Cursor');

#################################################################
#        9.Section of script body responsible                   #
#           for placing controls' values                        #
#      in appropriate places in "controlvalues" array           #
#      and placing received cursor in $cursor variable          #
#################################################################
if($test){
print '<FORM ACTION=';
print $cgiurl;
print 'METHOD=POST>';
$colindex = 0;
foreach $control (@controls){
 print '<CENTER>';
 $temp = $controls[$colindex];
 print $temp ;
 print '</CENTER><BR>';	
 print '<CENTER>';
 print '<INPUT TYPE="text" NAME="';
 print $control;
 print '"';
 print ' VALUE="';
 $temp = $controlvalues[$colindex];
 print $temp;
 print '" >';
 print '</CENTER>';	
 print '<BR >';
 $colindex = $colindex + 1;
}
if($colindex){
print '<BR><BR>';
print '<CENTER>';
print '<INPUT TYPE="Submit" NAME="submit"';
print '</CENTER>';
print '</FORM>';
}
}

#################################################################
#        10.Section of script body where    generated SQL        #
#statement template is to be placed with appropriate insertions #
#     and   responsible  for getting  final sight  of SQL       #
################################################################# 
$stmtval = "\$stmt"."= \""."$stmt0"."\"";
eval  $stmtval;

#################################################################
#             11.Section of script body                          #
#     responsible  for making connection to DBASE and           #
#         getting count of rows                                 #
################################################################# 
$plat = "\$db"." = "."new "."$osplat"."::"."$dbplat"."("."$dbname".")";
eval $plat;
if (!$db){
 &print_db_error;
 return;
}
$lor = $db->Sql($stmt);
if ($lor){
 &print_sql_error;
 return;
}
$rowtotal = &get_total_rows;

#################################################################
#             12.Section of script body                         #
#        responsible  for output "Query results" line           #
#             along with total count of rows                    #
#################################################################
#### only for debugging purpose
if($test){
print $stmtval,p;
}
print   "<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 WIDTH=100%><TR VALIGN=\"top\" ALIGN=\"center\" bgcolor=\"#aaaacc\">";
print   "<TD COLSPAN=2><H1> $head1</H1></TD></TR>";
print   "<TR VALIGN=\"top\" ALIGN=\"center\" bgcolor=\"#aaaacc\">";
print   "<TD COLSPAN=2><H1>$head2</H1></TD></TR>";
print   "</TABLE>";
print   "<center><U><B>QUERY RESULTS - $rowtotal matches</B></U><center>";
print   "<BR><BR>";

#################################################################
#             13.Section of script body                         #
#        responsible  for calculation range of                  #
#              query portion for feedback page                  #
#################################################################
$start = $cursor;
$end = $cursor + $quota;
if($end >= $rowtotal){
$end = $rowtotal;
$cursornext = 0;
}
else
{
 $cursornext = $cursor + $quota;
} 
$cursorprev = $cursor - $quota;

#################################################################
#             14.Section of script body                         #
#        responsible  for calculation range of                  #
#                   query portion                               #
#           output them in outgoing stream                      #
#################################################################
&print_range_line($rowtotal,$start,$end);

#################################################################
#             15.Section of script body                         #
#        responsible  for output of table captions              #
#            for query columns on feedback page                 #
#################################################################
 print   "<TABLE BORDER=$border CELLSPACING=0 CELLPADDING=0 WIDTH=100%><TR VALIGN=\"top\" ALIGN=\"$align\" bgcolor=\"#aaaacc\">";
 $capindex = 0;
 @fieldnames = $db->FieldNames();
 foreach $caption (@captions){
 if($caption eq ""){
  &print_header_cell($fieldnames[$capindex]);
 } else{
  &print_header_cell($captions[$capindex]);
 }$capindex = $capindex + 1;
 }

#################################################################
#             16.Section of script body                         #
#        responsible  for fetching rows from DBASE              #
#            and placing values in the cells of table           #
#################################################################
$lor = $db->Sql($stmt);
$j=0;
while($db->FetchRow()){
 if($j < $start ){
  $j++;
  next;
 }
 if($j >= $end ){
 last;
 }
 undef %hash;
 %hash = $db->DataHash;
 $bgcolor = $colors[$j%2];
 print "<TR VALIGN=\"top\" ALIGN=\"$align\" bgcolor=\"$bgcolor\">";
 $i=0;
 foreach $column (@columns){
  if($columntype[$i] EQ "NONE"){
   &print_cell($hash{$column});
  }
  else{
    $_=$columntype[$i];
   if($columntype[$i] EQ "URL"){
    &print_url_cell($hash{$column});
   }
   if($columntype[$i] EQ "MAIL"){
    &print_mail_cell($hash{$column});
   }
   if($columntype[$i] EQ "IMAGE"){
    &print_image_cell($hash{$column});
   }
    if($columntype[$i] EQ "BOOL"){
    &print_bool_cell($hash{$column});
   }
   if(/^DATE_(\d+)$/){
   $datform=$1;
   &print_date_cell($hash{$column},$datform);
   }
   if($columntype[$i] EQ "TIME"){
   &print_time_cell($hash{$column});
   }
  }
  $i = $i + 1;
 }
 $j = $j + 1;	
}
print "</TABLE>";
print   "<BR>";
print   "<BR>";
#### end of session ##########
$db->Close();

#################################################################
#             17.Section of script body   (NEXT)                #
#        responsible  for generation and placing in             #
#            output stream the form tags as invisible           # 
#     fields for getting query information from feedback page   #
#################################################################
#if it is needed to place NEXT button
if($cursornext > 0){
 print '<FORM ACTION=';
 print $cgiurl;
 print 'METHOD=POST>';
 $colindex = 0;
 foreach $control (@controls){
  &print_field("hidden",$control,$controlvalues[$colindex]);  
  $colindex = $colindex + 1;
 }
 &print_field("hidden","Cursor",$cursornext);
 &print_field("Submit","","Next");
 print "</FORM>";
}

#################################################################
#             18.Section of script body   (PREVIOUS)            #
#        responsible  for generation and placing in             #
#            output stream the form tags as invisible           # 
#     fields for getting query information from feedback page   #
#################################################################
#if it is needed to place PREVIOUS button
if($cursorprev >= 0){
 print '<FORM ACTION=';
 print $cgiurl;
 print 'METHOD=POST>';
 $colindex = 0;
 foreach $control (@controls){
 &print_field("hidden",$control,$controlvalues[$colindex]);  
 $colindex = $colindex + 1;
}
 &print_field("hidden","Cursor",$cursorprev);
 &print_field("Submit","Submit","Previous");
 print "</FORM>";
}
if($pageURL NE ""){
 print "<BR><A HREF =\"$pageURL\" TARGET=\"_top\">Go Back</A>";
}
print end_html;
###

sub print_field
{
    local($type, $name, $value) = @_;
    print '<INPUT TYPE="';
    print  $type;
    print  '" NAME="';
    print  $name;
    print  '" VALUE="';
    print  $value;
    print  '">';
}
sub print_image_cell
{
 local($name) = @_;
 $name = &get_converted_string($name);
 print "<TD COLSPAN=1><P ALIGN = $align>";
 if(!$link){
 print "<IMG  SRC=\"$name\"  BORDER=0  ALT=\"$name\"></P></TD>";
 }
 else{
  print "<A HREF =\"$name\"><font size=-1 face=arial>$name</font></A></P></TD>";
 }
}
sub print_date_cell
{
local($datetime,$datformat) = @_;
($date,$time)=split(' ',$datetime);
$date = &get_converted_string($date);
$wholestring = &format_date($date,$datformat);
&print_cell($wholestring);
}
sub print_time_cell
{
local($datetime) = @_;
($date,$time)=split(' ',$datetime);
&print_cell($time);
}
sub print_mail_cell
{
 local($name) = @_;
 $name = &get_converted_string($name);
 print	"<TD COLSPAN=1><P ALIGN = $align>";
 print "<A HREF =\"mailto:$name\"><font size=-1 face=arial>$name</font></A></P></TD>";
}
sub print_url_cell
{
 local($name) = @_;
 $name = &get_converted_string($name);
 print	"<TD COLSPAN=1><P ALIGN = $align>";
 print "<A HREF =\"$name\"><font size=-1 face=arial>$name</font></A></P></TD>";
}
sub print_cell
{
 local($name) = @_;
 $name = &get_converted_string($name);
 print	"<TD COLSPAN=1><P ALIGN = $align>";
 print "<font size=-1 face=arial>$name</font></P></TD>";	    
}
sub print_header_cell
{
 local($name) = @_;
 print	"<TD COLSPAN=1><P ALIGN = $align>";
 print "<B><font size=+1 face=arial>$name</font></B></P></TD>";	    
}
sub print_range_line
{
 local($rowtotal,$start,$end) = @_;
if($rowtotal){
 print   "<center>From ";
 print eval($start +1);
 print	" to ";
 print	$end;
 print	"</center>";
 print   "<BR>";
}
}

sub get_converted_string
{
  local($name) =@_;
  $name=~ s/&/&amp;/g;
  $name=~ s/</&lt;/g;
  $name=~ s/>/&gt;/g;
  $name=~ s/Š/&copy;/g;
  $name=~ s/Ž/&reg;/g;
  $name=~ s/"/&quot;/g;
  $name=~ s/\n/<BR>/g;
  $name=~ s/''/'/g;
  return $name;
}
sub get_control_values
{
 local(@controls)=@_;
 local($cindex) = 0;
 foreach $control (@controls){
 if(($t=param($control))){
   if($controlvalues[$cindex] EQ 'NO'){
    $controlvalues[$cindex] = 'YES';
   }
   if($controlvalues[$cindex] EQ ''){
	$t=~ s/'/''/g;
	$t=~ s/\|//g;
    $controlvalues[$cindex] =$t;
   }
 }
 $cindex = $cindex + 1;
}
}
sub format_date
{
 @months =('January','February','March','April','May','June','July','August','September','October','November','December');
 @mon = ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
 #dd-MMM-yyyy = 0
 #MM/dd/yyyy = 1
 #dd MMMM,yyyy = 2
 #MMMM dd,yyyy = 3 
 local($date,$dataformat) = @_;
 ($y,$m,$d)=split('-',$date);
 if($dataformat == "0"){
  $m = $m -1;
  return "$d $months[$m],$y"
 }
 if($dataformat == "1"){
  return "$d-$mon[$m]-$y";
 }
 if($dataformat == "2"){
  $m = $m -1;
  return "$m/$d/$y";
 }
 if($dataformat == "3"){
  $m = $m -1;
  return "$months[$m] $d,$y";
 }
}
sub print_bool_cell
{
  local($name) = @_;
  if($name == "0"){
    &print_cell("NO");
  }
  else{
  &print_cell("YES");
  }
}
sub get_total_rows
{
local($i)=0;
while($db->FetchRow()){
 $i = $i+1;
}
local($rowtotal) = $i;
return $i;
}

sub print_db_error
{
print "<H1>DATASOURCE OPEN ERROR:</H1>";
print "<P>No such DSN: <u>$dbname</u></P>";
}
sub print_sql_error
{
 print "<H1>QUERY EXECUTION ERROR:</H1>";
 print "<P>QUERY STATEMENT: <U>$stmt</U></P>";
 print "<P>DIAGNOSTICS: <U>";
 print $db->Error();
 print "</U></P>";
}
