#====================================
#Navigation navigate_Records Buttons 1-4
# 1 Beginning
# 2 Previous 
# 3 Next
# 4 End	

sub navigate_Records {

	my $kind = $query->param('kind');
	my $rowpointer = $query->param('searchterm'); 
    my ($sth, $stmt);
	my @row;
    warn("Entered update_tables.cgi Open Database return First Record with action: = '$action'");   
	warn("Search Parameter = '$kind'");
	warn("rowpointer = '$rowpointer'");	

	my $minrowpointer;
	my $maxrowpointer;
	my $searchfield;

	warn("sortindex =  '$sortindex'"); #Set by selection in search_Records
	if (!$sortindex) {
	  $sortindex = 0;
	}
	if ($sortindex == 0){
	    $searchfield = 'user_id';
		$minrowpointer = 1;
	    $maxrowpointer = 14999;
	   } 
	elsif ($sortindex == 1) {
        $searchfield = 'lastname';	
		$minrowpointer = 'a%';
	    $maxrowpointer = 'z%';
	   }
	elsif ($sortindex == 2) {
	    $searchfield = 'business';	
		$minrowpointer = 'a%';
	    $maxrowpointer = 'z%';
       } 	   
	
	

	#kind 0 & 3 ok with direct hard coded SQL 
	
	if ($kind == 0){  
		#beginning record Tested Good

        $stmt= "SELECT * FROM ( SELECT * FROM users WHERE $searchfield <= ? AND $searchfield >= ? ORDER BY $searchfield ASC LIMIT 1 ) sub ORDER BY $searchfield ASC"; 

        # example user_id 
#		$stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <= 14999 AND user_id >= 1 ORDER BY user_id ASC LIMIT 1 ) sub ORDER BY user_id ASC"; #Works fine hard coded
		
        #example lastname SELECT * FROM ( SELECT * FROM users WHERE lastname <='z%' AND lastname >= 'a%'ORDER BY lastname ASC LIMIT 1 ) sub ORDER BY lastname ASC #Works fine hard coded

		#example business SELECT * FROM ( SELECT * FROM users WHERE business <='z%' AND business >= 'a%'ORDER BY business ASC LIMIT 1 ) sub ORDER BY business ASC 
	}
	
	if ($kind == 1){
		#previous record Tested Good
		$stmt = "SELECT * FROM users WHERE $searchfield <= (SELECT MAX($searchfield) FROM users WHERE $searchfield < ?) ORDER BY $searchfield DESC LIMIT 1";
	}                                            
  
	if ($kind == 2){
		#next record Tested Good
		$stmt = "SELECT * FROM users WHERE $searchfield >= (SELECT MIN($searchfield) FROM users WHERE $searchfield > ?) ORDER BY $searchfield ASC LIMIT 1";
   
	} 

	if ($kind == 3){
		#Last record Tested Good
		
        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE $searchfield <= ? AND $searchfield >= ? ORDER BY $searchfield DESC LIMIT 1 ) sub ORDER BY $searchfield ASC"; 
		
        #example user_id 
#		$stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <=14999 AND user_id >= 1 ORDER BY user_id DESC LIMIT 1 ) sub ORDER BY user_id ASC"; #Works fine hard coded
        		
		#example lastname SELECT * FROM ( SELECT * FROM users WHERE lastname >='z%' AND lastname >= 'a%'ORDER BY user_id DESC LIMIT 1 ) sub ORDER BY lastname ASC #Works fine hard coded

        #example business SELECT * FROM ( SELECT * FROM users WHERE business >='z%' AND business >= 'a%'ORDER BY business DESC LIMIT 1 ) sub ORDER BY business ASC 
	}

	warn("statement =  '$stmt'");
	
	$sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt . "\nDBI returned: \n", $dbh->errstr;

#	warn("'$maxrowpointer' '$minrowpointer' '$searchfield' '$rowpointer'");

   	if (($kind == 0) || ($kind == 3)){
	   warn("kind = '$kind' maxrowpointer = '$maxrowpointer'  minrowpointer = '$minrowpointer'"); 
	   $sth->execute ($maxrowpointer,$minrowpointer) or die "Unable to execute query: " . $sth->errstr;
     } 
	elsif (($kind == 1) || ($kind == 2)){
	   warn("kind = '$kind' rowpointer = '$rowpointer'"); 
       $sth->execute($rowpointer) or die "Unable to execute query: " . $sth->errstr;	
	}
     
    my $navResult = $sth->fetchrow_hashref();
	
	warn("navResult = '$navResult'");
	
    my $count = $sth->rows;

    warn("count = '$count'");
if ($count == 0) {
	  my %navFail = ( Count => $count);
	  my $json = JSON->new; 
	  $json->canonical(1);
	  $json = encode_json(\%navFail);
  	  print "Content-Type: application/json\n\n";
	  print $json;
	  warn("Finished print 0 count $json");	
      exit(1);     }
    else {
	  warn("count = '$count'");
      my $json = JSON->new; 
	  $json->canonical(1);
	  $json = encode_json($navResult);
	  print "Content-Type: application/json\n\n";	  
	  print $json;
	  warn("Finished print $json");	
      exit(0);
	}

}

##</code><code>##

#Search Records & Return Found 3 buttons
# 0 Member ID
# 1 Last Name 
# 2 Business Name 
#elsif ($action eq "search_Records")

 sub search_Records {
    warn("Entered search_Records");
    my $kind = $query->param('kind');
 	my $searchterm = $query->param('searchterm');
	my $result;
	warn("searchterm = '$searchterm'");
    my $searchfield = "";

    if ($kind == 0) {
	  $searchfield = 'user_id';
	} 
	if ($kind == 1) {
   	  $searchfield = 'lastname';	
	}
	if ($kind == 2) {
	  $searchfield = 'business';	
	}

    $sortindex = $kind;
	
    restoreOrder($kind);

    #Sort Index For The Three Kinds Of ORDER Supposed to be global.
	
    warn("sortindex =  '$sortindex'");
	
	warn("searchfield =  '$searchfield'");
	
    $stmt = "SELECT * FROM users WHERE $searchfield = ? ORDER BY ? ASC";
#    my $stmt = "SELECT * FROM users WHERE user_id = 15 ORDER BY user_id ASC";
	
	warn("statement =  '$stmt'");	
	
    $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt . "\nDBI returned: \n", $dbh->errstr;
    
    $sth->execute($searchterm, $searchfield) or die "Unable to execute query: " . $sth->errstr;	
#    $sth->execute() or die "Unable to execute query: " . $sth->errstr;	

    my $searchResult = $sth->fetchrow_hashref();

    my $count = $sth->rows;

    warn("count = '$count'");
    if ($count == 0) {
	  warn("Failed Search: '$searchfield' equal to '$searchterm' "); 
      my %searchFail = ( SearchError => $kind);
	  my $json = JSON->new; 
	  $json->canonical(1);
	  $json = encode_json(\%searchFail);
  	  print "Content-Type: application/json\n\n";
	  print $json;
	  warn("Finished print 0 count $json");	
      exit(1);
     }  #{"SearchError":0} Good
    else {
	  warn("count = '$count'");
      my $json = JSON->new; 
	  $json->canonical(1);
	  $json = encode_json($searchResult);
	  print "Content-Type: application/json\n\n";
	  print $json;
	  warn("Finished print $json");	
      exit(0);
	}
}

##</code><code>##


#==================================
#Set up "order by clause" depending on searchfield
sub restoreOrder{ 
    my $searchIndex = shift;  
   
    if ($searchIndex == 0)  {
       $searchfield = "user_id"; 

     }
    elsif ($searchIndex == 1)  {
       $searchfield = "lastname";

     }
    elsif ($searchIndex == 1)  {
	   $searchfield =  "business";
	}

   warn("in restoreOrder index = '$searchIndex'");
	
	my $stmt = "alter table users order by $searchfield asc";
	
    my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt . "\nDBI returned: \n", $dbh->errstr;	
	
	DBI->trace(3,$dbitracelog); 
	
	$sth->execute() or die "Unable to execute query: " . $sth->errstr;	

}